Q

Latest row for each group

I have a table with two fields, RequestID and RequestDate. I want a query to return the latest distinct RequestIDs. In the sense, if there are four records of RequestID 1 and three records of RequestID 2, I would like to get back only two records, which have the latest RequestID 1 and the latest RequestID 2.

The standard SQL approach to this problem is really quite straightforward:

select RequestID 
     , RequestDate
  from yourtable T
 where RequestDate
     = ( select max(RequestDate)
           from yourtable
          where RequestID = T.RequestID
       )

This query uses a correlated subquery. Note the use of the T alias to correlate the rows of the subquery to the row of the outer query. In effect, correlation like this is another way to do grouping.

However, if you are using MySQL, versions prior to 4.1, the above will not work. In that case, try this:

select T1.RequestID 
     , T1.RequestDate
  from yourtable T1
inner
  join yourtable T2
    on T1.RequestID = T2.RequestID
group
    by T1.RequestID 
     , T1.RequestDate
having T1.RequestDate = max(T2.RequestDate)

This query uses a self join, joining the table to itself on matching keys, and an explicit GROUP BY clause to perform the same grouping as the first query, with a HAVING clause to filter the rows of the group. Not surprisingly, it produces the same result set. (As the saying goes, "Your mileage may vary; mine doesn't.")

This query works in other databases, too, not just MySQL. I cannot comment on which query is more efficient, though, since each database's optimizer is different; however, I expect a good optimizer to produce efficient, if not identical, execution plans for both queries.

See also Latest two rows for each group.


This was first published in December 2003

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close