Need More SQL Query NewID Help

[Paradyse]

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.ShowcaseRid es.RecordID,
dbo.ShowcaseRid es.CustomerID, dbo.ShowcaseRid es.PhotoLibID,
dbo.ShowcaseRid es.Year,
dbo.ShowcaseRid es.MakeShowcase ,
dbo.ShowcaseRid es.ModelShowcas e, dbo.ShowcaseRid es.VehicleTitle ,
dbo.ShowcaseRid es.NickName,
dbo.ShowcaseRid es.SiteURL,
dbo.ShowcaseRid es.ShowcaseRati ng, dbo.ShowcaseRid es.ShowcaseRati ngImage,
dbo.ShowcaseRid es.ReviewDate,
dbo.ShowcaseRid es.Home,
dbo.ShowcaseRid es.EntryDate, dbo.Customers.U serName,
dbo.Customers.S hipCity, dbo.Customers.S hipRegion,
dbo.Customers.S hipPostalCode,
dbo.Customers.S hipCountry, dbo.Customers.L astName,
dbo.Customers.F irstName, dbo.Customers.M emberSince,
dbo.ShowcaseRid es.Live,
dbo.ShowcaseRid es.MemberLive, dbo.Accessories .Make,
dbo.Accessories .Model, Photos.Path
FROM dbo.ShowcaseRid es INNER JOIN
dbo.Customers ON dbo.ShowcaseRid es.CustomerID =
dbo.Customers.C ustomerID INNER JOIN
dbo.Accessories ON dbo.ShowcaseRid es.MakeShowcase
= dbo.Accessories .MakeShowcase AND
dbo.ShowcaseRid es.ModelShowcas e =
dbo.Accessories .ModelShowcase INNER JOIN
(SELECT MIN(dbo.Showcas ePhotos.PhotoPa th)
AS Path, RecordID
FROM dbo.ShowcasePho tos
GROUP BY RecordID) Photos ON
dbo.ShowcaseRid es.RecordID = Photos.RecordID INNER JOIN
dbo.ShowcasePho tos ON Photos.Path =
dbo.ShowcasePho tos.PhotoPath
WHERE (dbo.ShowcaseRi des.MemberLive = 1) AND (dbo.ShowcaseRi des.Live
= 1) AND (dbo.ShowcaseRi des.MakeShowcas e = @MMColParam)
ORDER BY dbo.ShowcaseRid es.EntryDate DESC

Regards,

Darin L. Miller
Paradyse Development
~-~-~-~-~-~-~-~-~-~-~-~-~-~-
"Some things are true whether you believe them or not." - Nicolas Cage
in City of Angels

[Erland Sommarskog]

Paradyse (su*****@parady sed.com) writes:

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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Paradyse]

I removed the DISTINCT and it showed the same record 10 times. Who is
Hugo?

"Erland Sommarskog" <es****@sommars kog.sewrote in message
news:Xn******** *************@1 27.0.0.1:

Paradyse (su*****@parady sed.com) writes:

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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Paradyse (su*****@parady sed.com) writes:

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.

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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx