Simple Grouping Query Question

[mattjp]

Hi, I'm relatively green at SQL queries, and I was wondering what the best way to construct a t-sql query on this data:

table: activitylog
fields: serial#, starttime, stoptime, activity

'serial' is non-unique, 'serial' + 'starttime' is unique. I need a query that will return, for each serial number, the row with the most recent (greatest) 'starttime' timestamp.

For example, for this table:

SN# start stop act
1234 12:00 12:15 a
1235 13:34 13.45 a
1234 13:50 14:02 b

I need to be able to get:
SN# start stop act
1235 13:34 13.45 a
1234 13:50 14:02 b

What is the best way to do this? Group By doesn't work this way, and I don't think it can be done with a join. Thanks for the help.

 

[gmmastros]

This question is very similar.

thread183-1501957

Please read through it. There is a great explanation near the bottom that explains how you can get your results. If you have any further questions, just let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mattjp]

Thanks George, your reply in the other thread was very helpful!