Hello
In the table below, how can the records be filtered so that only the max Col2 is taken per group of Col1 values?
Col1 | Col2 | Col3 |
---|---|---|
A | 01/01/2000 | 1 |
A | 02/01/2000 | 2 |
A | 03/01/2000 | 3 |
B | 04/01/2000 | 4 |
B | 05/01/2000 | 5 |
B | 06/01/2000 | 6 |
Expected result:
Col1 | Col2 | Col3 |
---|---|---|
A | 03/01/2000 | 3 |
B | 06/01/2000 | 6 |
I tried the below without luck:
select * from Table1 having Col2=max(Col2) group by Col1
I remember there is a way to group records then sort then take the first but I wonder if there is a simpler way?
Thanks!