Home > Enterprise >  Take oldest record in a set of rows
Take oldest record in a set of rows

Time:04-04

So, I have this problem, I have this set of records in a table

TemperatureID CastingID TemperatureDateTime TemperatureValue
1421294 1073513 2021-01-07 11:53:00.000 1648
1421295 1073513 2021-01-07 11:54:00.000 1698
1421326 1073514 2021-01-07 22:00:00.000 1594
1421327 1073514 2021-01-07 22:11:00.000 1609

and this repeated many times. My problem is that I have to take only the oldest record for each set of CastingID just like that:

TemperatureID CastingID TemperatureDateTime TemperatureValue
1421294 1073513 2021-01-07 11:53:00.000 1648
1421326 1073514 2021-01-07 22:00:00.000 1594

I tried using DISTINCT and MIN functions, but I can't obtain the result I want, can you please help me or provide some examples?

CodePudding user response:

You can try this,

SELECT * 
FROM (
    SELECT 
        *
        , RANK() OVER (PARTITION BY CastingID ORDER BY TemperatureDateTime ASC) RN
    FROM TABLE_1
) A
WHERE RN = 1;
  • Related