skip to Main Content

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

2

Answers


  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

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