Geek asks and answers

How to select one row when grouping by column and maximum date?

I have the following data that I would like to filter out, so I get only one row based on the grouping of the first column and select the maximum 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 i want:

 1   |  125 | 2014
 2   |  215 | 2018

I tried using a few examples that I found here (as shown below), as well as another group by / distinct / max (date), but 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
+5
source share
3 answers

Change the section in row_number()only to the section col1, but keep the order 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 .

col1 col2, . .

+3

bluefeet, MAX:

SELECT t.col1, t.col2, t.date
FROM yourtable t
    JOIN (
        SELECT col1, MAX(date) maxDate
        FROM yourtable
        GROUP BY col1
    ) t2 on t.col1 = t2.col1 AND t.date = t2.maxDate

SQL Fiddle Demo ( )

0
    Select * from yourtable where date in 
(select max(date) from tab group by col1);
0
source

More articles:


All Articles