Select unique rows by max value in column

Question

I have a table with timezones which looks like this sample:

CREATE TABLE timezones
(`CountryCode` varchar(2),
 `ZoneName` varchar(35),
 `Offset` int not null,
 `Dst` char(1),
 `TimeStart` decimal(11,0) not null);

INSERT INTO 
timezones (`CountryCode`, `ZoneName`, `Offset`, `Dst`, `TimeStart`)

VALUES
('VA', 'Europe/Vatican', 7200, '1', 1521939600),
('VA', 'Europe/Vatican', 3600, '0', 1540688400),
('VA', 'Europe/Vatican', 7200, '1', 1553994000),

('UA', 'Europe/Zaporozhye', 10800, '1', 1521939600),
('UA', 'Europe/Zaporozhye', 7200, '0', 1540688400),
('UA', 'Europe/Zaporozhye', 10800, '1', 1553994000),

('TR', 'Europe/Istanbul', 7200, '0', 1446944400),
('TR', 'Europe/Istanbul', 7200, '1', 1459040400),
('TR', 'Europe/Istanbul', 10800, '0', 1473195600);

+-------------+-------------------+--------+------+------------+
| CountryCode | ZoneName          | Offset | Dst  | TimeStart  |
+-------------+-------------------+--------+------+------------+
| VA          | Europe/Vatican    |   7200 | 1    | 1521939600 |
| VA          | Europe/Vatican    |   3600 | 0    | 1540688400 |
| VA          | Europe/Vatican    |   7200 | 1    | 1553994000 |
| UA          | Europe/Zaporozhye |  10800 | 1    | 1521939600 |
| UA          | Europe/Zaporozhye |   7200 | 0    | 1540688400 |
| UA          | Europe/Zaporozhye |  10800 | 1    | 1553994000 |
| TR          | Europe/Istanbul   |   7200 | 0    | 1446944400 |
| TR          | Europe/Istanbul   |   7200 | 1    | 1459040400 |
| TR          | Europe/Istanbul   |  10800 | 0    | 1473195600 |
+-------------+-------------------+--------+------+------------+

I need to select rows with unique ZoneName field that have the biggest value in TimeStart column.

So for the above table, I expect the following result:

+-------------+-------------------+--------+------+------------+
| CountryCode | ZoneName          | Offset | Dst  | TimeStart  |
+-------------+-------------------+--------+------+------------+
| VA          | Europe/Vatican    |   7200 | 1    | 1553994000 |
| UA          | Europe/Zaporozhye |  10800 | 1    | 1553994000 |
| TR          | Europe/Istanbul   |  10800 | 0    | 1473195600 |
+-------------+-------------------+--------+------+------------+

Found an answer [here](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column). There are solutions: **1:** `select t1.* from timezones t1 INNER JOIN (select ZoneName, Max(TimeStart) TimeStart FROM timezones GROUP BY ZoneName) t2 ON t1.ZoneName = t2.ZoneName AND t1.TimeStart = t2.TimeStart;` or **2:** `select t1.* from timezones t1 LEFT OUTER JOIN timezones t2 ON t1.ZoneName = t2.ZoneName AND t1.TimeStart < t2.TimeStart WHERE t2.ZoneName IS NULL;` — Andrey Dengin, Jun 06 '19 at 15:03

score 0 · Answer 1 · answered Jun 06 '19 at 14:11

0

This is a filtering operation. You can use a correlated subquery:

select t.*
from t
where t.timestart = (select max(t2.timestart) from t t2 where t2.ZoneName = t.ZoneName);

answered Jun 06 '19 at 14:11

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Jun 06 '19 at 14:13

You could use a subquery for max time and join

select * from timezones t1
inner join (
    select  CountryCode, ZoneName , max(1553994000) max_t
    from timezones 
) t2 on t1.CountryCode = t2.CountryCode
    and t1.ZoneName = t2.ZoneName 
      and t1.TimeStart = t2.max_t

Select unique rows by max value in column

2 Answers2