CODEWITHSUNDEEP
sql
user550738
user550738

Reputation:

How to select a record and include its newest related record in a SQL query?

I'm a Java guy and I need some SQL pointers. Here's a simplified version of what I'm trying to do...

Let's says I have:

Basically, I need to write a query that gives me a list of all the users who are currently online.

So far I have this ...

SELECT u.id, u.username, s.state_name, us.date_time 
FROM USERS u
JOIN USER_STATE us ON u.id = us.user_id
JOIN STATE s ON us.state_id = s.id
WHERE us.state_id = 1;

... but that gives me a record for every "online" entry in the USER_STATE table.

The question is: How do I only get the users whose newest state is "online"?

Upvotes: 0

Views: 50

Answers (5)

Soundappan A
Soundappan A

Reputation: 331

i have worked with your query. Based on your question i created below tables and inserted records as you mentioned.

create table usertab
(id int,username varchar(100))


create table statetab
(id int,state_name varchar(100))


create table userstatetab
(id int,user_id int,state_id int,date_time time)


insert usertab values(0,'Robert')
insert usertab values(1,'Bob')
insert usertab values(2,'Joe')


insert statetab values(0,'OFFLINE')
insert statetab values(1,'ONLINE')


insert userstatetab values(0,0,1,'08:50')
insert userstatetab values(1,1,1,'09:00')
insert userstatetab values(2,0,0,'09:10')
insert userstatetab values(3,1,0,'10:00')
insert userstatetab values(4,1,1,'12:00')
insert userstatetab values(5,1,0,'12:05')
insert userstatetab values(6,0,1,'13:10')
insert userstatetab values(6,0,0,'14:10')



--getting last online status of all users
with lastonline as
(
SELECT 
ROW_NUMBER() over(partition by us.user_id order by us.date_time desc) r_no,u.id, 
u.username, 
s.state_name, 
us.date_time 
FROM usertab u
JOIN userstatetab us ON u.id = us.user_id
JOIN statetab s ON us.state_id = s.id
WHERE us.state_id = 1
) select id, username, state_name, date_time from lastonline where r_no=1 ;

Sample output

ID | username| state_name| date_time
---+---------+-----------+-----------------
0  | Robert  | ONLINE    | 13:10:00.0000000
1  | Bob     | ONLINE    | 12:00:00.0000000

Upvotes: 1

Popeye
Popeye

Reputation: 35920

You can use EXISTS as the following:

SELECT U.USERNAME, S.STATE_NAME, US.DATE_TIME
  FROM USER_STATE US
  JOIN STATE S ON (US.STATE_ID = S.ID)
  JOIN USERS U ON (US.USER_ID = U.ID)
 WHERE S.STATE_NAME = 'ONLINE'
   AND NOT EXISTS 
       (SELECT 1 
          FROM USER_STATE USIN 
         WHERE US.DATE_TIME > USIN.DATE_TIME)

db<>fiddle demo

Cheers!!

Upvotes: 0

Alessio Cantarella
Alessio Cantarella

Reputation: 5201

If the id of the user_state table is an auto-increment field, in the subquery you can take the max id grouping by user_id:

SELECT users.id
  , users.name
FROM users
JOIN user_state
  ON users.id = user_state.user_id
WHERE user_state.id IN (
  SELECT MAX(id)
  FROM user_state
  GROUP BY user_id
)
  AND user_state.state_id = 1

Upvotes: 0

Himanshu
Himanshu

Reputation: 3970

You can try via group by with having to have the record of the most recent date. 

    select u.id, u.username, 
     max(s.state_name) , 
     max(us.date_time) 
     from USERS u
      join USER_STATE us on u.id = 
        us.user_id
       join STATE s on us.state_id = s.id
         group by u.id, u.username
         having 
          1=(case 
            when 
             us.datetime=max(datetime) then 
            1 else 0 end)
            ) ;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can use a correlated subquery to get the most recent record and then check that it is online:

select us.*
from user_state us 
where us.date_time = (select max(us2.date_time)
                      from user_state us2
                      where us2.user_id = us.user_id
                    ) and
      us.state_id = 1;

For more information, about users, just join in the users table in the outer query.

Upvotes: 0

Related Questions