The best answers to the question “how do I query sql for a latest record date for each user” in the category Dev.
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.
Using window functions (works in Oracle, Postgres 8.4, SQL Server 2005, DB2, Sybase, Firebird 3.0, MariaDB 10.3)
select * from ( select username, date, value, row_number() over(partition by username order by date desc) as rn from yourtable ) t where t.rn = 1
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
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 )
I see most of the developers use an inline query without considering its impact on huge data.
Simply, you can achieve this by:
SELECT a.username, a.date, a.value FROM myTable a LEFT OUTER JOIN myTable b ON a.username = b.username AND a.date < b.date WHERE b.username IS NULL ORDER BY a.date desc;