SQL - get only row with min delta time between columns Items-CodePudding

Here I have the Table1 which has two Columns time ; Time1 and Time2 .
The Time1 is the same for a given Item , while Time2 depends on the Code .
For every item what I need is only the row whose delta Abs( Time2 - Time1 ) in the lowest.

Which are the following rows:

CodePudding user response：

Use a Rank() window function on DeltaTime, then filter for rank=1 in an outer query.

Select * From (
    Select Item, Time1, Code, Time2, DeltaTime,
        Rank() Over (Partition By Item Order By DeltaTime) as TimeRank
    From Table1
    )
Where TimeRank=1

This will report both records if there is a tie for lowest. If you want only one record in case of a tie then substitute a row_number() function for the rank() function.

Alternatively,

You can use a correlated subquery like this:

Select * From Table1 X
Where DeltaTime= (Select min(DeltaTime) From Table1 Y
                  Where X.Item=Y.Item)

This is slightly more compact and easier to read but many DBMS implementations can't execute them efficiently.

CodePudding user response：

If I understand your question correctly, and the first picture is the table you have, you could just do following:

SELECT *
FROM Table1
GROUP BY Item
HAVING min(DeltaTime);

CodePudding user response：

If I understand your question correctly, and the first picture is the table you have, you could just do the following:

SELECT * FROM Table1 
GROUP BY Item HAVING min(DeltaTime);