January 17, 2005 at 9:11 am
I need to get the latest 2 sightings for a car abnd this SQL gives me that:
SELECT SIGHTING_TABLE.car_id,
SIGHTING_TABLE.car_type,
SIGHTING_TABLE.sighting_date_time,
SIGHTING_TABLE.review_date_time,
SIGHTING_TABLE.trip_id
FROM SIGHTING_TABLE, TRIP_TABLE WHERE
SIGHTING_TABLE.car_id = TRIP_TABLE.car_id AND
SIGHTING_TABLE.trip_id = TRIP_TABLE.trip_id AND
AND sighting_date_time in
(Select top 2 sighting_date_time from SIGHTING_TABLE B
where SIGHTING_TABLE.car_id = B.car_id AND SIGHTING_TABLE.trip_id = B.trip_id
Order by sighting_date_time desc)
Order by SIGHTING_TABLE.review_date_time desc, SIGHTING_TABLE.car_id,
SIGHTING_TABLE.sighting_date_time desc
But eh way the results are passed back need to be changed. I need it sortest by latest review date - which it does, but I need the cars grouped together.
Results Before:
car_id.....review_date_time
XYZ......1-2-2005 08:00
DEF......1-2-2005 08:00
ABC......1-1-2005 09:00
XYZ......1-1-2005 08:00
ABC......1-1-2005 07:00
Here's what I want:
car_id.....review_date_time
XYZ......1-2-2005 08:00
XYZ......1-1-2005 08:00
DEF......1-2-2005 08:00
ABC......1-1-2005 09:00
ABC......1-1-2005 07:00
January 17, 2005 at 9:33 am
First, I don't understand why the review_date_time of your "Resuts Before" is in ascending order, when the Order by clause of your SQL statement says "SIGHTING_TABLE.review_date_time desc"
However, have you tried:
Order by SIGHTING_TABLE.car_id desc, SIGHTING_TABLE.review_date_time.car_id,
SIGHTING_TABLE.sighting_date_time desc
Of course this won't give you the exact results you are looking for. This order by clause will return your sample data in the following order:
car_id.....review_date_time
XYZ......1-1-2005 08:00
XYZ......1-2-2005 08:00
DEF......1-2-2005 08:00
ABC......1-1-2005 08:00
ABC......1-1-2005 09:00
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply