CODEWITHSUNDEEP
sqloracle11g
grandeale83
grandeale83

Reputation: 141

INNER JOIN on change status

I have a table USERS with 3 columns user_id, user_status and usr_update. This last column says the date and time of the last change of status, I want to get the last change of status of a user. I try this but doesn't work.

SELECT user_id, user_status, max(usr_update)
FROM USERS u1 INNER JOIN USERS u2
    ON u1.user_id=u2.user_id
WHERE u1.user_id='123456' AND
    u1.user_status<>u2.user_status

anyone can help?

@jarlh it's ok but what if I have more consecutive record with the same state and I want to get the firs record with the new state? for example

I want to get the record (id_15,blocked,17/10/2015)

Upvotes: 0

Views: 92

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you only care about one user, then use order by and fetch only one row:

SELECT u.*
FROM (SELECT u.*
      FROM users u
      WHERE u.user_id = '123456' 
      ORDER BY u.usr_update DESC
     ) u
WHERE rownum = 1;

(Note: In Oracle 12+, you can use FETCH FIRST 1 ROW ONLY so the subquery is not needed.)

You can also do this using keep:

SELECT u.user_id, MAX(u.usr_update),
       MAX(user_status) KEEP (DENSE_RANK FIRST OVER ORDER BY u.usr_update DESC) as last_user_status
FROM users u
WHERE u.user_id = '123456' 
GROUP BY u.user_id;

This method will work without the WHERE clause. And, in my experience, KEEP performs quite well.

Upvotes: 0

jarlh
jarlh

Reputation: 44796

SELECT user_id, user_status, usr_update
FROM USERS u1
WHERE NOT EXISTS (select 1 from users u2
                  where u1.user_id=u2.user_id
                    and u2.usr_update > u1.usr_update)

I.e. return a row if there are no later row for the same user.

Upvotes: 1

Related Questions