How do you get 1 record per group in MySQL when dates match?

Question

This is not a duplicate as far as I can tell as the others all have DIFFERENT dates. As an example, I have the following table of company, meter, and last date serviced:

Company    MeterCode  Name  LastDate
A          1          MeA   1/1/20
A          2          MeB   1/1/20
A          3          MeC   1/2/20
A          4          MeD   1/2/20

B          1          MeE   2/1/20
B          2          MeF   2/1/20
B          3          MeG   2/2/20
B          4          MeH   2/2/20

How do I query this table to get 1 row per company returning all columns but with the most recent date? Multiple meters can have the same last serviced date as you see but I don't care which meter I end up with as long as it's the most recent date. In the example above I'd want to end up with something like:

Company    MeterCode  Name  LastDate
A          4          MeD   1/2/20
B          4          MeH   2/2/20

score 1 · Accepted Answer · answered Apr 21 '20 at 00:07

1

For MySql versions prior to 8.0+ you can use a correlated subquery in the WHERE clause:

select t.* from tablename t
where t.metercode = (
  select metercode from tablename
  where company = t.company
  order by lastdate desc, metercode desc limit 1
)

See the demo.
Results:

| Company | MeterCode | Name | LastDate  |
| ------- | --------- | ---- | ----------|
| A       | 4         | MeD  | 01/02/20  |
| B       | 4         | MeH  | 02/02/20  |

answered Apr 21 '20 at 00:07

forpas

160,666
10
38
76

This works perfectly, thank you! However, it's apparently inefficient and takes a long time (6s or so per run) so I ended up returning all meters that fit a criteria, add 1 record per company to an array and return that array. – Lifz Apr 21 '20 at 13:13

score 0 · Answer 2 · answered Apr 20 '20 at 23:57

This is a variation of the greatest-n-per-group query that is asked frequently on Stack Overflow.

Here's a solution that works in MySQL 8.0, which supports window functions:

SELECT t.Company, t.MeterCode, t.Name, t.LastDate
FROM (
    SELECT Company, MeterCode, Name, LastDate,
      ROW_NUMBER() OVER (PARTITION BY Company ORDER BY LastDate DESC) AS rownum
    FROM WhateverYourTableNameIs
) AS t
WHERE t.rownum = 1

You may ask, "what if I'm not using MySQL 8.0?"

MySQL 8.0 has now been in GA release for two years. I think it's time to start telling people they should upgrade if they want the features it supports.

How do you get 1 record per group in MySQL when dates match?

2 Answers2