Simple SQL grouping 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.

 

[genomon]

Check the FAQs at forum183.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[r937]

SELECT "serial#"
     , starttime
     , stoptime
     , activity
  FROM activitylog 
INNER
  JOIN ( SELECT "serial#"
              , MAX(starttime) AS maxtime
           FROM activitylog
         GROUP
             BY "serial#" ) AS m
    ON m."serial#" = activitylog."serial#"
   AND m.maxtime = activitylog.starttime

r937.com | rudy.ca

 

[mattjp]

Thanks--you solved my problem completely!