quote:

---

Originally posted by rowter

Hi,

I have a table that has picktime, route_id, stlat and stlong columns.
There might be multiple records having same picktime and routeid.


Select MAX(S.pickup_time), S.route_id from STARTTBL as S where Start_Dt= '7/11/2013' group by S.Route_id
(pktime) route_id stlat stlat
08:50 17 -32.01 102.98
09:50 17 -33.01 102.98
14:25 25
16:30 25
16:00 29

1. Is it possible to get the all the 4 columns where pickup_time is MAX for each Route_id

For the above data , the resulting data would be

09:50 17 -33.01 102.98
16:30 25
16:00 29

Thanks

---

SELECT *
FROM 
(
SELECT ROW_NUMBER() OVER (PARTITION BY route_id ORDER BY pktime DESC) AS Seq,*
FROM table
)t
WHERE Seq=1

SELECT *
FROM 
(
SELECT ROW_NUMBER() OVER (PARTITION BY route_id ORDER BY DATEDIFF(minute,0,pktime) DESC) AS Seq,*
FROM table
)t
WHERE Seq=1