Need More SQL Query NewID Help

Excuse my ignorance because I don't do advance db related programming,
but have no other choice at the moment. My experience is limited to
simple queries.

I need to have the following query display the recordset in random order
based on RecordID (unique key) if possible. I tried the ORDER BY NewID()
at the end and it generated an error (ORDER BY items must appear in the
select list if SELECT DISTINCT is specified.) I guess because of the sub
query. I would also like for the recordset to display a different 10
records on each hit to the page, not just the same 10 records in random
order. I wasn't sure if the SELECT commands I have in place are
sufficient for this task.

Thanks in advance for any assistance.

SELECT DISTINCT
TOP 10 dbo.ShowcaseRides.RecordID,
dbo.ShowcaseRides.CustomerID, dbo.ShowcaseRides.PhotoLibID,
dbo.ShowcaseRides.Year,
dbo.ShowcaseRides.MakeShowcase,
dbo.ShowcaseRides.ModelShowcase, dbo.ShowcaseRides.VehicleTitle,
dbo.ShowcaseRides.NickName,
dbo.ShowcaseRides.SiteURL,
dbo.ShowcaseRides.ShowcaseRating, dbo.ShowcaseRides.ShowcaseRatingImage,
dbo.ShowcaseRides.ReviewDate,
dbo.ShowcaseRides.Home,
dbo.ShowcaseRides.EntryDate, dbo.Customers.UserName,
dbo.Customers.ShipCity, dbo.Customers.ShipRegion,
dbo.Customers.ShipPostalCode,
dbo.Customers.ShipCountry, dbo.Customers.LastName,
dbo.Customers.FirstName, dbo.Customers.MemberSince,
dbo.ShowcaseRides.Live,
dbo.ShowcaseRides.MemberLive, dbo.Accessories.Make,
dbo.Accessories.Model, Photos.Path
FROM dbo.ShowcaseRides INNER JOIN
dbo.Customers ON dbo.ShowcaseRides.CustomerID =
dbo.Customers.CustomerID INNER JOIN
dbo.Accessories ON dbo.ShowcaseRides.MakeShowcase
= dbo.Accessories.MakeShowcase AND
dbo.ShowcaseRides.ModelShowcase =
dbo.Accessories.ModelShowcase INNER JOIN
(SELECT MIN(dbo.ShowcasePhotos.PhotoPath)
AS Path, RecordID
FROM dbo.ShowcasePhotos
GROUP BY RecordID) Photos ON
dbo.ShowcaseRides.RecordID = Photos.RecordID INNER JOIN
dbo.ShowcasePhotos ON Photos.Path =
dbo.ShowcasePhotos.PhotoPath
WHERE (dbo.ShowcaseRides.MemberLive = 1) AND (dbo.ShowcaseRides.Live
= 1) AND (dbo.ShowcaseRides.MakeShowcase = @.MMColParam)
ORDER BY dbo.ShowcaseRides.EntryDate DESC

Regards,

Darin L. Miller
Paradyse Development
~-~-~-~-~-~-~-~-~-~-~-~-~-~-
"Some things are true whether you believe them or not." - Nicolas Cage
in City of AngelsParadyse (support@.paradysed.com) writes:

Quote:

Originally Posted by

Excuse my ignorance because I don't do advance db related programming,
but have no other choice at the moment. My experience is limited to
simple queries.
>
I need to have the following query display the recordset in random order
based on RecordID (unique key) if possible. I tried the ORDER BY NewID()
at the end and it generated an error (ORDER BY items must appear in the
select list if SELECT DISTINCT is specified.) I guess because of the sub
query. I would also like for the recordset to display a different 10
records on each hit to the page, not just the same 10 records in random
order. I wasn't sure if the SELECT commands I have in place are
sufficient for this task.

Hugo has already asked why you have the DISTINCT there. Have you tried
simply to remove it?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I removed the DISTINCT and it showed the same record 10 times. Who is
Hugo?

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns97FA1AC01EC0Yazorman@.127.0.0.1:

Quote:

Originally Posted by

Paradyse (support@.paradysed.com) writes:

Quote:

Originally Posted by

Excuse my ignorance because I don't do advance db related programming,
but have no other choice at the moment. My experience is limited to
simple queries.

I need to have the following query display the recordset in random order
based on RecordID (unique key) if possible. I tried the ORDER BY NewID()
at the end and it generated an error (ORDER BY items must appear in the
select list if SELECT DISTINCT is specified.) I guess because of the sub
query. I would also like for the recordset to display a different 10
records on each hit to the page, not just the same 10 records in random
order. I wasn't sure if the SELECT commands I have in place are
sufficient for this task.

>
Hugo has already asked why you have the DISTINCT there. Have you tried
simply to remove it?
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Paradyse (support@.paradysed.com) writes:

Quote:

Originally Posted by

I removed the DISTINCT and it showed the same record 10 times.

Looks like you have to refine the query to weed out the duplicates then.
Probably you have an in sufficient join condition somewhere.

Without knowledge of the tables, and how they are related, it's difficult
to assist. At a very minimum we would need to see the table definition
including keys. You can script table definitions from Enterprise Manager
or SQL Server Managerment Studio, whichever you are using. Make us a
favour and remove [] and COLLATE clauses before you post it.

You could also try to cut down the query and removing tables until you
no longer get the duplicates. The point would be to track down how the
duplicates are introduced, and then you can work from there.

Quote:

Originally Posted by

Who is Hugo?

Hugo is Hugo Kornelis, another SQL Server MVP. I'm fairly sure that he
responded to one of your earlier posts.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Need help writing a SQL Server Query

Could someone please help me out? I need to write a sql stored proc to query the following table.
My SQL experience is very week. If someone can help me with this, I will be happy to pay you$40 for
your help.

I need the proc to do the following:
1.) For every Superintendent in a region, country state and county; return the state name, superintendent name,
the county name and and a string which is a comma delimited list of schools they supervise. See the sample output italicised and bold.

So the big challenge here is to also return a string that is a concatenation of school names for a particular
Superintendent in a given state and county. For example:East,Kennedy,Apolo,Morrison.

So basically the stored proc should accept input parameters of the Region, Country, State, and County

Here is the data table:

REGION COUNTRY STATE SUPER_INTENDENT PHONE_NO SCHOOL County

NA USA Texas Mike Andrews 789-3614 East Lake
NA USA Texas Mike Andrews 789-3614 Kennedy Lake
NA USA Texas Mike Andrews 789-3614 Apolo Lake
NA USA Texas Mike Andrews 789-3614 Morrison Lake
NA USA Texas Amy Markson 789-2134 Anderson Maylor
NA USA Texas Amy Markson 789-2134 Molina Maylor
NA USA Texas Amy Markson 789-2134 Polima Maylor
NA USA Ohio Terry Ellis 966-8314 Kingston Keel
NA USA Ohio Terry Ellis 966-8314 Martin Keel
NA USA Ohio Terry Ellis 966-8314 Eastmore Keel
NA USA Ohio Terry Ellis 966-8314 Canondale Keel

Here is the sample output the way it will appear on a web form:

State:Texas

County:Lake

Mike Andrews East,Kennedy,Apolo,Morrison
789-3614

County:Maylor

Amy Markson
789-2134 Anderson,Molina,Polima

State:Ohio

County:Keel

Terry Ellis Kingston,Martin,Eastomore,Keel

You can mail me the check..

You could create a stored proc that would concatenate the values based on given state and county.

Declare

@.schoolvarchar(500)

select

@.school=isnull(@.school,'')+','+ school

from

yourTable

where

state= @.state and county=@.county

Given the above hint, I hope you can figure out the rest.

|||Sorry your post has not helped me at all. I need someone to write the query for me. I will be happy to pay that person. please do not respond to this post if you are not serious about helping me.|||

ndinakar:

You can mail me the check..

That was meant to be a joke. I forgot to put a "smiley face" at the end. I was trying to show you how to write the code yourself since what you are trying to do is pretty simple. Now, I do agree that if you cannot figure out how to put the rest of the puzle together, you definetely need someone to write you the code. And perhaps you will need to spend more than the $40 you are offering..

|||

I appreciate your sense of humor. No problem.

Another fellow on the forum has offered to help me. I will see take his advice and try to get it working. Thank you.

Need help with XML output to file

I'm using SQL Server 2005 / 9.0.3042

I'm not new to sql server, but making my first experience with xml in sql server 2005.

I have a query like this (based on <Table> with neccessary data):

SELECT TAG, PARENT, <columns...>

FROM <Table>

FOR XML EXPLICIT

This query creates a xml file exactly as i need it when i execute it in Management Studio. Well, with one exception. It does not write the <xml...> tag at the beginning of the xml file. But i'm sure i get that in there somewho else. What i need to do now is get that output to a file on disk. And that's where my problem starts.

I tried SQLCMD within Management Studio, but that doesn't accept the ':XML ON' tag and ignores it. the resulting file written is not usable, as it also contains query summary information.

Any direction would be greatly appreciated!

Have you given DTS/SSIS a try. If you are having to do this procedure often, I would go with one of those.

|||

I could not find any way to choose xml as the destination for ssis. Could you give me a start on how to go on?

|||Hi Danny,
As you said that you are facing this problem in sql2005, so can u please tell me the querry by which i can generate a xml file through table in sql2000,
My basic question to you is,
HOW TO GENERATE AN XML FILE USING A SQL QUERY IN QUERY ANALYZER.?
is it possible.|||

Hi Prashant,

There are various ways to create a xml file. In general, u create a select statement and use "FOR XML xxxxx" at the end. Please have a look in Books Online for the possible <xxxxxx>. I would say it depends on purpose u want to achieve, you would choose the appropriate <xxxxxx> method. For each method u need to have its own data base.

I for my case needed to choose the FOR XML EXPLICIT method, as i need to reproduce a specific xml file dynamically, based on certain data. Running the query will create a xml file and give it as the result, so i can open it, and if i need copy the content. If "FOR XML EXPLICIT" is your choice, here is a simple example. I haven't done much on the other <xxxxxx> methods, so i'm sorry i won't be much of help. Method FOR XML EXPLICIT is the most time consuming way, but it gives almost every control to produce exactly the xml file needed.

OK: Here the sample for FOR XML EXPLICIT:

Let's suppose we need a xml file like this:

<Order>

<OrderItem Title="book1" Price="250.00"/>

<OrderItem Title="book2" Price="15.75" Discount=5.00/>

</Order>

For this we need to create a table holding the data to create the xml file using FOR XML EXPLICIT. This table must look like this: In the vertical it will have 1 row for ea line in the xml file. in the horizontal it needs the sum of all possible attributes. Enter a value will print the value in the xml file, enter empty string will print empty string in xml file, enter NULL as value will remove the attribute in the xml file. The table also needs the informatione to tell FOR XML EXPLICIT how the hierachy of the xml file must be. That is done using the TAG and PARENT attribut. The columns in the table must exactly match the names of the elements and attributes in the xml file, plus the level (number between - see blow).

Ok, here is the table:

TAG PARENT [Order!1] [OrderItem!2!Title] [OrderItem!2!Prive] [OrderItem!2!Discount]

-

1 NULL '' NULL NULL NULL

2 1 NULL book1 250.00 NULL

3 1 NULL book2 15.75 5.00

-

This is only a very simple example of FOR XML EXPLICIT, but i hope it makes clear on how it works. I used this way to generate our xml files dynamically. It was much work to build the system, but gives me much flexibility to construct all the various different xml files. Last but not least, it's only useful if the structure of the xml file don't change so often.

Need help with XML output to file

I'm using SQL Server 2005 / 9.0.3042

I'm not new to sql server, but making my first experience with xml in sql server 2005.

I have a query like this (based on <Table> with neccessary data):

SELECT TAG, PARENT, <columns...>

FROM <Table>

FOR XML EXPLICIT

This query creates a xml file exactly as i need it when i execute it in Management Studio. Well, with one exception. It does not write the <xml...> tag at the beginning of the xml file. But i'm sure i get that in there somewho else. What i need to do now is get that output to a file on disk. And that's where my problem starts.

I tried SQLCMD within Management Studio, but that doesn't accept the ':XML ON' tag and ignores it. the resulting file written is not usable, as it also contains query summary information.

Any direction would be greatly appreciated!

Have you given DTS/SSIS a try. If you are having to do this procedure often, I would go with one of those.

|||

I could not find any way to choose xml as the destination for ssis. Could you give me a start on how to go on?

|||Hi Danny,
As you said that you are facing this problem in sql2005, so can u please tell me the querry by which i can generate a xml file through table in sql2000,
My basic question to you is,
HOW TO GENERATE AN XML FILE USING A SQL QUERY IN QUERY ANALYZER.?
is it possible.|||

Hi Prashant,

There are various ways to create a xml file. In general, u create a select statement and use "FOR XML xxxxx" at the end. Please have a look in Books Online for the possible <xxxxxx>. I would say it depends on purpose u want to achieve, you would choose the appropriate <xxxxxx> method. For each method u need to have its own data base.

I for my case needed to choose the FOR XML EXPLICIT method, as i need to reproduce a specific xml file dynamically, based on certain data. Running the query will create a xml file and give it as the result, so i can open it, and if i need copy the content. If "FOR XML EXPLICIT" is your choice, here is a simple example. I haven't done much on the other <xxxxxx> methods, so i'm sorry i won't be much of help. Method FOR XML EXPLICIT is the most time consuming way, but it gives almost every control to produce exactly the xml file needed.

OK: Here the sample for FOR XML EXPLICIT:

Let's suppose we need a xml file like this:

<Order>

<OrderItem Title="book1" Price="250.00"/>

<OrderItem Title="book2" Price="15.75" Discount=5.00/>

</Order>

For this we need to create a table holding the data to create the xml file using FOR XML EXPLICIT. This table must look like this: In the vertical it will have 1 row for ea line in the xml file. in the horizontal it needs the sum of all possible attributes. Enter a value will print the value in the xml file, enter empty string will print empty string in xml file, enter NULL as value will remove the attribute in the xml file. The table also needs the informatione to tell FOR XML EXPLICIT how the hierachy of the xml file must be. That is done using the TAG and PARENT attribut. The columns in the table must exactly match the names of the elements and attributes in the xml file, plus the level (number between - see blow).

Ok, here is the table:

TAG PARENT [Order!1] [OrderItem!2!Title] [OrderItem!2!Prive] [OrderItem!2!Discount]

-

1 NULL '' NULL NULL NULL

2 1 NULL book1 250.00 NULL

3 1 NULL book2 15.75 5.00

-

This is only a very simple example of FOR XML EXPLICIT, but i hope it makes clear on how it works. I used this way to generate our xml files dynamically. It was much work to build the system, but gives me much flexibility to construct all the various different xml files. Last but not least, it's only useful if the structure of the xml file don't change so often.