CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
user3007447
user3007447

Reputation: 400

Finding row of "duplicate" data with greatest value

I have a table setup as follows:

Key || Code || Date
5       2      2018
5       1      2017
8       1      2018
8       2      2017

I need to retrieve only the key and code where:

Code=2 AND Date > the other record's date

So based on this data above, I need to retrieve:

Key 5 with code=2

Key 8 does not meet the criteria since code 2's date is lower than code 1's date

I tried joining the table on itself but this returned incorrect data

Select key,code 
from data d1
Join data d2 on d1.key = d2.key
Where d1.code = 2 and d1.date > d2.date

This method returned data with incorrect values and wrong data.

Upvotes: 1

Views: 82

Answers (2)

Ven
Ven

Reputation: 2014

use row_number, u can select rows with dates in ascending order. This is based on your sample data, selecting 2 rows

DECLARE @table TABLE ([key] INT, code INT, DATE INT)

INSERT @table
SELECT 5, 2, 2018

UNION ALL

SELECT 5, 2, 2018

UNION ALL

SELECT 8, 1, 2018

UNION ALL

SELECT 8, 2, 2017

SELECT [key], code, DATE
FROM (
    SELECT [key], code, DATE, ROW_NUMBER() OVER (
            PARTITION BY [key], code ORDER BY DATE
            ) rn
    FROM @table
    ) x
WHERE rn = 2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Perhaps you want this:

select d.*
from data d
where d.code = 2 and
      d.date > (select d2.date
                from data d2
                where d2.key = d.key and d2.code = 1
               );

If you just want the key, I would go for aggregation:

select d.key
from data d
group by d.key
having max(case when d2.code = 2 then date end) > max(case when d2.code <> 2 then date end);

Upvotes: 1

Related Questions