Select the ID of last log

Question

I've an SQL select that is pretty simple to write in English but I can't achieve to translate it in SQL. Here it is:

I want the ID of the latest event by user.

The table looks like this:

| COLUMN_NAME | DATA_TYPE |
|-------------|-----------|
| ID          | NUMBER    |
| OCCURED     | DATE      |
| USER_ID     | NUMBER    |

And I've try a lot of manipulations using Max(), some GroupBys:

SELECT l.user_id, MAX(l.OCCURED)
FROM USER_STATS.LOG l
group by l.user_id, l.occured
order by l.OCCURED DESC

but I didn't succeed.

How could I do this?

score 1 · Answer 1 · answered Dec 09 '15 at 15:40

1

SELECT l.user_id, MAX(l.OCCURED) as OCCURED 
FROM USER_STATS.LOG l
group by l.user_id
order by OCCURED DESC

I.e only GROUP BY user_id.

answered Dec 09 '15 at 15:40

jarlh

42,561
8
45
63

score 1 · Accepted Answer · answered Dec 09 '15 at 19:45

I reopened the question, because I think this is the simplest method:

One method that uses a single aggregation is to use first:

SELECT l.user_id, MAX(l.OCCURED),
       MAX(ID) KEEP (DENSE_RANK FIRST ORDER BY l.OCCURRED DESC) as id_at_max
FROM USER_STATS.LOG l
GROUP BY l.user_id;

Select the ID of last log

2 Answers2