I have a MySQL table people
like this:
id | person_id | meeting_time |
---|---|---|
1 | 21 | 123456 |
2 | 21 | 123457 |
3 | 21 | 123456 |
4 | 22 | 123470 |
5 | 21 | 0 |
6 | 21 | 123472 |
I need to get 1 row for each person_id
, but only, if meeting_time
is between 123451 and 123460. If it is not I don't need the row.
Also I don't need a lower id with higher meeting_time (like id 2) nor I need id 5. However currently I'm getting id 2, instead of id 3.
Here is my query:
SELECT MAX(`id`) AS `id`, `person_id`, `meeting_time`
FROM `people`
WHERE `meeting_time` > 123451 AND `meeting_time` < 123460
ORDER BY `id` DESC
Edit: The answer given by SelVazi works, so far, however: Since there is id 6
(I expanded the table with this entry), which has meeting_time
greater than the max range I should not get any entries, but I'm getting id 3
(which was correct before I remembered I need that too).
Edit 2: MySQL server 5.5
You can do it using inner join
:
This is to look for the max id
grouped by person_id
then check if meeting_time
is between 123451 and 123460
with cte as (
select p.*
from people p
inner join (
SELECT person_id, max(id) as id
FROM people
where meeting_time between 123451 and 123460
group by person_id
) as s on s.id = p.id
)
select c.*
from cte c
inner join (
SELECT person_id, MAX(meeting_time) as max_meeting_time
FROM people
group by person_id
) as s on s.person_id = c.person_id and s.max_meeting_time between 123451 and 123460
This is a working query for mysql 5.5
select c.*
from (
select p.*
from people p
inner join (
SELECT person_id, max(id) as id
FROM people
where meeting_time between 123451 and 123460
group by person_id
) as s on s.id = p.id
) as c
inner join (
SELECT person_id, MAX(meeting_time) as max_meeting_time
FROM people
group by person_id
) as s on s.person_id = c.person_id and s.max_meeting_time between 123451 and 123460