0

I am trying to query from a table like this:

ID product revenue data device
01 A 10 2022-06-17 08:02:00.693 mobile
01 A 10 2022-06-17 10:02:00.693 desk
03 G 10 2022-06-17 09:02:00.693 mobile
03 G 10 2022-06-17 10:02:00.693 desk

As you can see the table contains duplicates for ID, product and revenues. I would like to select the duplicate with the most oldest date like this:

ID product revenue data device
01 A 10 2022-06-17 08:02:00.693 mobile
03 G 10 2022-06-17 09:02:00.693 mobile

I'm looking for an efficient query, I tried with the statement TOP, but it doesn't work

Database: T-SQL

john_white80
  • 1
  • 1

2 Answers2

1

You have mentioned the most recent date, but in the expected result you have mentioned records with old date.

You can get most recent data by using desc in order by. If you remove desc, it would give eldest of each date.

select a.* from 
( select t.* , 
 ROW_NUMBER() OVER (PARTITION BY ID, product , revenue ORDER BY data DESC) 
 AS rn 
from your_table t
) a
where a.rn=1
Utsav
  • 7,914
  • 2
  • 17
  • 38
-1

You can use 'order by desc' on Data column to get the most recent one at the top and so on.

Sid
  • 37
  • 5