CODEWITHSUNDEEP
sqloracle-database
Standage
Standage

Reputation: 1517

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

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

Upvotes: 5

Views: 3482

Answers (3)

Mukesh Dhiman
Mukesh Dhiman

Reputation: 99

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

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

I prefer bluefeet's method, but here is an equivalent using 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 (borrowed from other post)

Upvotes: 0

Taryn
Taryn

Reputation: 247650

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.

Upvotes: 3

Related Questions