CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
onigame
onigame

Reputation: 214

Get records with max value for each group based on second table

This is related to Get records with max value for each group of grouped SQL results, except that the necessary groups are in a separate table.

Let's say that I have a couple of buildings, each building has some offices, and I have some people who "check in" to those offices to do work.

This table, called "offices", matches buildings to offices:

building   office
---
Seuss      Yertle
Seuss      Cubbins
Milne      Pooh
Milne      Eeyore
Milne      Roo

This table, called "checkins", records when people worked in each office:

id   office   person   timestamp
---
1    Yertle   Aaron    100
2    Cubbins  Aaron    200
3    Pooh     Aaron    300
4    Cubbins  Charlie  300
5    Cubbins  Aaron    700
6    Eeyore   Beth     600
7    Pooh     Beth     400

I'd like to generate a table that tells me, for each building-person combo, which check-in was the most recent one for that person in that building:

 building  person   checkin_id  office   timestamp
 ---
 Seuss     Aaron    5           Cubbins  700
 Milne     Aaron    3           Pooh     300
 Milne     Beth     6           Eeyore   600
 Seuss     Charlie  4           Cubbins  300

I'm at a loss for how do I do this. The standard trick involves joining a table to itself while comparing the relevant value, then throwing away the rows where there is no bigger value. I assume I'll need two copies of "checkins" and two copies of "buildings" with a complex join between them, but I can't seem to get the NULLs to show up in the correct place.

I'm using MySQL, if that helps.

Upvotes: 0

Views: 89

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use corelated subquery

 select b.* from
  (select o.building,c.person,c.id as checkinid,
   c.office,c.timestamp from
   offices o join checkins c
   on o.office=c.office
  ) b
  where b.timestamp = (select max(a.timestamp)
                      from (
                        select o.building,c.person,
                        c.office,c.timestamp from
                        offices o join checkins c
                        on o.office=c.office
                         ) as a  where a.building=b.building and  
                     a.person=b.person 
                   )
       order by person

output

building    person  checkinid   office  timestamp
Milne       Aaron     3         Pooh     300
Seuss       Aaron     5         Cubbins  700
Milne       Beth      6         Eeyore   600
Seuss       Charlie   4         Cubbins  300

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

There is a trick using group_concat() and substring_index() that allows you to do this with a single group by:

select o.building, c.person,
       max(c.id) as checkinid,
       substring_index(group_concat(c.office order by timestamp desc), ',', 1) as office,
       max(c.timestamp) as timestamp
from offices o join
     checkins c
     on o.office = c.office
group by o.building, c.person;

This version assumes that id and timestamp increase together, so max() can be used for both.

Also, group_concat() -- by default -- has a limit of about 1,000 characters for the intermediate result, so this won't work if there are lots and lots of offices for a person/building combination or if the offices have long names. Of course, the separator can be changed if commas appear in the office name.

Upvotes: 1

Nick
Nick

Reputation: 147166

To get the result you want, you have to create a table which has all checkins in each office (JOIN offices to checkins) and then JOIN that to a table of the latest checkin for each person in each building:

SELECT o.building, c.person, c.id AS checkin_id, c.office, c.timestamp
FROM offices o
JOIN checkins c ON c.office = o.office
JOIN (SELECT o.building, c.person, MAX(c.timestamp) AS timestamp
      FROM offices o
      JOIN checkins c ON c.office = o.office
      GROUP BY o.building, c.person) t ON t.building = o.building AND t.person = c.person AND t.timestamp = c.timestamp
ORDER BY c.person, c.office

Output:

building    person      checkin_id  office      timestamp
Seuss       Aaron       5           Cubbins     700
Milne       Aaron       3           Pooh        300
Milne       Beth        6           Eeyore      600
Seuss       Charlie     4           Cubbins     300

Demo on dbfiddle

Upvotes: 0

Related Questions