I'm not a SQL person but something like this would seem to do it:
SELECT Image1, Description, Rating
FROM cars
WHERE Rating = (SELECT MAX(RATING) FROM cars WHERE approved = 1)
In other words (working from inner to outer) get the highest rated car,
then select all the data that matches that Rating. (If two cars have
the same rating them you'll get mutliple rows).
You might also try a "top" type command, but this is more DB specific:
SELECT TOP 1 Image1, Description, Rating
FROM cars
WHERE WHERE approved = 1
ORDER BY Rating
In this case (my syntax might be a little off) you're saying "get all
the cars and sort them by rating, then just return the first one ("top
1").
Hope this helps,
Jim Davis
> -----Original Message-----
> From: Chad [mailto:cgray@;careyweb.com]
> Sent: Sunday, November 03, 2002 10:54 AM
> To: CF-Talk
> Subject: Query help
>
>
> I know I have done this in the past, but I cannot figure it
> out for some reason.
>
> I want to grab out of a database the record with the highest
> value for the field RATING (it is an INT). I also want to
> grab all the data for the record like Image1, Description etc....
>
>
> SELECT MAX(RATING),
> FROM cars
> WHERE approved = 1
>
> Something is very wrong with my select statement.. I cant
> figure out if I have to do a MAX(RATING) AS MaximumRATING,
> Image1, Description...
>
>
> Need more coffee!
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
