how do I query sql for a latest record date for each user

Question

I have a table that is a collection entries as to when a user was logged on.

username, date,      value
--------------------------
brad,     1/2/2010,  1.1
fred,     1/3/2010,  1.0
bob,      8/4/2009,  1.5
brad,     2/2/2010,  1.2
fred,     12/2/2009, 1.3

etc..

How do I create a query that would give me the latest date for each user?

Update: I forgot that I needed to have a value that goes along with the latest date.

Answer 1

You would use aggregate function MAX and GROUP BY

SELECT username, MAX(date), value FROM tablename GROUP BY username, value

Answer 2

SELECT Username, date, value
 from MyTable mt
 inner join (select username, max(date) date
              from MyTable
              group by username) sub
  on sub.username = mt.username
   and sub.date = mt.date

Would address the updated problem. It might not work so well on large tables, even with good indexing.

Answer 3

This one should give you the correct result for your edited question.

The sub-query makes sure to find only rows of the latest date, and the outer GROUP BY will take care of ties. When there are two entries for the same date for the same user, it will return the one with the highest value.

SELECT t.username, t.date, MAX( t.value ) value
FROM your_table t
JOIN (
       SELECT username, MAX( date ) date
       FROM your_table
       GROUP BY username
) x ON ( x.username = t.username AND x.date = t.date )
GROUP BY t.username, t.date

Answer 4

To get the whole row containing the max date for the user:

select username, date, value
from tablename where (username, date) in (
    select username, max(date) as date
    from tablename
    group by username
)

Answer 5

select t.username, t.date, t.value
from MyTable t
inner join (
    select username, max(date) as MaxDate
    from MyTable
    group by username
) tm on t.username = tm.username and t.date = tm.MaxDate

Answer 6

SQL Server 2005 and up...

select * from (
    select
        t.username,
        t.date,
        t.value,
        row_number() over(partition by t.username order by t.date desc) as rn
    from
        yourtable t
) tt
where tt.rn = 1