Select latest ID only, if it matches criteria

Question

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

Answer 1

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

Demo here

---

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

Demo here

Answer 2

You can use a correlated subquery. It assumes that id is unique:

select *
from t
where id = (
    select max(id)
    from t as x
    where x.person_id = t.person_id
    and meeting_time between 123451 and 123460
);

Answer 3

A simple, old-fashioned LIMIT. No need for MAX.

SELECT `id`, `person_id`, `meeting_time` 
    FROM `people` 
    WHERE `meeting_time` > 123451 AND `meeting_time` < 123460 
    ORDER BY `id` DESC
    LIMIT 1

This would help with performance:

INDEX(meeting_time)

Works in (at least) MySQL 4.0 and later.