Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Simple SQL grouping question

Simple SQL grouping question

Simple SQL grouping question

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.

RE: Simple SQL grouping question


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

r937.com | rudy.ca

RE: Simple SQL grouping question

Thanks--you solved my problem completely!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close