Help with sort - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with sort

Author

Topic

zubby01
Starting Member

1 Post

Posted - 2005-01-17 : 14:09:32

I need to get the latest 2 sightings for a car and 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 the 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
LMN......1-3-2005 08: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
LMN......1-3-2005 08:00

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-17 : 16:35:58

Order by SIGHTING_TABLE.car_id, SIGHTING_TABLE.review_date_time desc,
SIGHTING_TABLE.sighting_date_time desc

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources