CODEWITHSUNDEEP
sqlpostgresql
darthzejdr
darthzejdr

Reputation: 314

SQL query, filtering based on last value in a connected table

I have a problem with a nested query (the problem beeing i have no idea how to do it) I'm using PostgreSQL

I have 2 tables:

users
id name
1 x
2 y
3 z

call
id user_id time data
1 1 00:10 stat1
2 1 00:15 stat2
3 3 00:10 stat2
4 3 00:30 stat1
5 3 00:45 stat2
6 3 00:50 stat3

What i need to get is a select of users, but only if their last call was stat2. The problem i'm having is i don't know how to get only the latest data checked.

My query:

SELECT users.*, call.* FROM users, call WHERE users.id=call.user_id AND call.id IN (SELECT id FROM call WHERE call.data='stat2') ORDER BY users.id

What i get with that is:

users
id name id user_id time data
1 x 2 1 00:15 stat2
3 z 3 3 00:10 stat2
3 z 5 3 00:45 stat2

What i need to get would be:

users
id name id user_id time data
1 x 1 1 00:15 stat2

The 3 shouldn't display as it's last value is stat 3 (and obviously shouldn't double since i have 2 stats.

i can do it with multiple queries, but i'd like to do it in 1.

Thanks

Upvotes: 1

Views: 268

Answers (2)

Mikhail
Mikhail

Reputation: 1560

Try using ALL statement:

SELECT users.*, call.* FROM users, call WHERE users.id=call.user_id AND call.id IN (SELECT id FROM call WHERE call.data='stat2') 
AND call.time >= all (select a.time from call a where a.user_id = users.id) ORDER BY users.id

That means that the time in selected row is >= time for all of the calls for this user.

Upvotes: 0

Eli Gassert
Eli Gassert

Reputation: 9763

So you want to get the last call id for each Users.id. You do that with a group by. Then once you get the last call id, you check if the status of that last call id is stat2.

The query looks like:

SELECT *
FROM USERS U
INNER JOIN
  (SELECT user_id, MAX(id) AS last_id
   FROM Calls c
   GROUP BY user_id
  ) t0
ON t0.user_id = u.id
INNER JOIN Calls c ON c.id = t0.last_id
WHERE c.type = 'stat2'

Example fiddle: http://www.sqlfiddle.com/#!1/7016e/5

Upvotes: 2

Related Questions