Question 20886 in Mysql Question posted in
The official MySQL documentation can be found here.

Select latest ID only, if it matches criteria – Mysql

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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search