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

Answer 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  |