how do I query sql for a latest record date for each user
select t.username, t.date, t.valuefrom MyTable tinner join ( select username, max(date) as MaxDate from MyTable group by username) tm on t.username = tm.username and t.date = tm.MaxDate
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) twhere t.rn = 1
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.valueFROM myTable aLEFT OUTER JOIN myTable bON a.username = b.username AND a.date < b.dateWHERE b.username IS NULLORDER BY a.date desc;