CODEWITHSUNDEEP
sqlselectdistinct
Yippie-Ki-Yay
Yippie-Ki-Yay

Reputation: 22834

Sql statement 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)?

Upvotes: 1

Views: 82

Answers (4)

Andrew Lazarus
Andrew Lazarus

Reputation: 19340

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;

Upvotes: 1

Stefan H
Stefan H

Reputation: 6683

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)

Upvotes: 0

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95731

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";

Upvotes: 1

Neville Kuyt
Neville Kuyt

Reputation: 29649

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.

Upvotes: 0

Related Questions