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

The best answers to the question “how do I query sql for a latest record date for each user” in the category Dev.

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:

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

ANSWER:

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:

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:

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;