SQL - Get Top 1 for each item in a list with many records-CodePudding

I have the following table:

ID | Plate | RecordedAt
   |       |
   |       |

For each plate I can have N records.

I'm working with the following query:

 SELECT TOP 1 * 
 FROM table
 WHERE table.Plate IN ('plate1', 'plate2', 'plate3') 
 ORDER BY c.Recordedat DESC

The problem is that this query returns only one record for all the list.

I need the top 1 of each item in the list.

Is there any way to achieve this with only one query?

CodePudding user response：

You would use window functions:

select t.*
from (select t.*,
             row_number() over (partition by plate order by recordedat desc) as seqnum
      from t
      where table.Plate in ('plate1', 'plate2', 'plate3')
     ) t
where seqnum = 1;

CodePudding user response：

You can use cte in sql for this situation like following:

with TopOneOfTables
as
{
    select  yt.*,
            row_number() over (partition by Plate order by RecordedAt desc) as RowNo
            from yourTable yt
      Where yt.Plate in ('plate1', 'plate2', 'plate3') 
}

select *
from TopOneOfTables
where RowNo = 1

CodePudding user response：

I think this should work. Basically you need to partition the data set by one column, but you also want to order each partition. Thats where keep dense_rank comes in.

select distinct plate, 
max(ID) keep(dense_rank FIRST order by RecordedAt 
desc nulls last ) over ( partition by plate ) as required_field 
from table 
where table.Plate in ('plate1', 'plate2', 'plate3') ;