Sql statement question

Question

I have a table, which contains logs and has the following scheme:

USER | DATE | LOG
x      x      x
...

Now, I want to make ONE query to retrieve every (USER, DATE) pair, where DATE is the latest for this user.

I was thinking about something like (pseudo):

SELECT ... FROM (TABLE) ORDERED BY DATE, DISTINCT BY USER

But I'm not sure if that's gonna work.

Is it correct that DISTINCT would take the first possible dates in this query, therefore yielding the required result? Or the order of elements in DISTINCT query is undefined?

---

If yes, how should I solve this problem (this is the case I can't add new table, such as users and, for example, cache the lastest dates there)?

Answer 1

Now, I want to make ONE query to retrieve every (USER, DATE) pair, where DATE is the latest for this user.

select user, max(date)
from yourtable
group by user

You might want to add ORDER BY user.

Since both user and date are reserved words for most SQL dbms, this is more likely to run.

select "user", max("date")
from "yourtable"
group by "user";

Answer 2

If your database supports windowing, you can do this with

SELECT user, date, log FROM
(SELECT user, date, log, row_number()
  OVER (PARTITION BY user ORDER BY date DESC) AS rn) FROM table_name) AS subq
WHERE rn=1;

Answer 3

DISTINCT on it's own will not quite do what you want, since you want the largest date PER USER, you need to do a subquery. The order by in the query will ensure that you get teh largest date.

    SELECT DISTINCT USER
         , DATE
         , LOG
      FROM TABLE AS T1
     WHERE DATE = (SELECT TOP 1 DATE
                     FROM TABLE AS T2
                    WHERE T2.USER = T1.USER
                 ORDER BY DATE DESC)

Answer 4

simplest solution would be

select user, max(date)
from tableName
group by user

This does what you ask - but if you add "log" to the query, you need to use a subquery.