How to select a single row when grouping by column and by max date?

Question

I have the following data which I would like to filter so I only get only one row based on the grouping of the first column and select the max date

co2 contains unique values

col1 | col2 | date

 1   |  123 | 2013
 1   |  124 | 2012
 1   |  125 | 2014
 2   |  213 | 2011
 2   |  214 | 2015
 2   |  215 | 2018

so the results I want are:

 1   |  125 | 2014
 2   |  215 | 2018

I've tried using a few examples which I found on here (as below) as well other group by / distinct / max(date) but with no luck

select t.*
from (select t.*,
             row_number() over (partition by col1, col2 order by date desc) as seqnum
      from t
     ) t
where seqnum = 1

Answer 1

Change the partition in the row_number() to only partition by col1 but keep the order by date desc:

select col1, col2, date
from 
(
  select col1, col2, date,
    row_number() over (partition by col1 
                       order by date desc) as rn
  from yourtable
) x
where rn = 1

See SQL Fiddle with Demo.

Since you were partitioning by both col1 and col2 you were getting unique values for each row. So it would not return the row with the max date.