SEO

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 Angels

Paradyse (support@paradysed.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, esquel@sommarskog.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

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

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns97FA1AC01EC0Yazorman@127.0.0.1:

> Paradyse (support@paradysed.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, esquel@sommarskog.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 (support@paradysed.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, esquel@sommarskog.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