I have the following query:
SELECT id, MAX(`grab_time`) AS `grab_time` FROM events WHERE ended = false GROUP BY id
This returns all the records that have the greatest grab_time as compared to other records that have the same id.
The table events has four columns though, and I want them all. I've been advised to put the above query in to a subquery, in order to get the missing columns (it only shows 2), but I don't know how to do that?
How can I tell sql to basically "SELECT everything WHERE the rows are from the rows in ()"?
You can use a correlated subquery:
select e.*
from events e
where not e.ended and
e.grab_time = (select max(e2.grab_time) from events e2 where e2.id = e.id and not e2.ended);
You want an index on events(id, ended, grab_time) for this query.
Or use row_number():
select e.*
from (select e.*, row_number() over (partition by id order by grab_time desc) as seqnum
from events e
where not ended
) e
where seqnum = 1;
You can use the IN as
select * from events
where (id,grab_time) in
(SELECT id, MAX(`grab_time`) AS `grab_time` FROM events WHERE ended = false GROUP BY id)
You can also use the MAX windows function as follows:
select * from
(
select t.*, max(grab_time) over (partition by id) as mx
from your_table where ended = false
)
where grab_time = mx
One approach can be using temporary tables:
with max_time as
(
select id,MAX(grab_time) as grab_time FROM events where ended='false' GROUP BY
id
)
select * from events e,max_time mt where e.id=mt.id and e.grab_time=mt.grab_time;