SQL Query conditional on date column-CodePudding

I have a table with historical option prices, and I would like to select for each day the contract(s) that have the closest expiration date. Below is a min examples:

Date         ExpDate   Unique Contract ID
1/1/2022     2/1/2022   1
1/1/2022     3/1/2022   2
2/1/2022     3/1/2022   3
2/1/2022     4/1/2022   4

For this table I would like to obtain this:

Date         ExpDate   Unique Contract ID
1/1/2022     2/1/2022   1
2/1/2022     3/1/2022   3

Not sure if this could be done with maybe a groupby? The query below isn't working as it won't allow me to select all the columns for some reason:

SELECT *, MIN(ExpDate) FROM table_name GROUP BY Date

CodePudding user response：

We can accomplish this with ROWNUMBER by getting the difference between the dates:

with ordered as
(
    select Date
        , ExpDate
        , [Unique Contract ID]
        , ROW_NUMBER() over (partition by Date order by DATEDIFF(dd, Date, ExpDate)) rn
    from tbl
)
select Date
    , ExpDate
    , [Unique Contract ID]
from ordered o
where o.rn = 1

This was done in T-SQL since you didn't provide a DBMS.

CodePudding user response：

In Oracle, and other RDBMS that support analytic functions, you can use:

SELECT "DATE", ExpDate, ID
FROM   (
  SELECT t.*,
         RANK() OVER (PARTITION BY "DATE" ORDER BY ExpDate - "DATE") AS rnk
  FROM   table_name t
) t
WHERE  rnk = 1;

Which, for the sample data:

CREATE TABLE table_name ("DATE", ExpDate, ID) AS
SELECT DATE '2022-01-01', DATE '2022-01-02', 1 FROM DUAL UNION ALL
SELECT DATE '2022-01-01', DATE '2022-01-03', 2 FROM DUAL UNION ALL
SELECT DATE '2022-01-02', DATE '2022-01-03', 3 FROM DUAL UNION ALL
SELECT DATE '2022-01-02', DATE '2022-01-04', 4 FROM DUAL;

Outputs:

DATE EXPDATE ID

2022-01-02 00:00:00 2022-01-03 00:00:00 3

2022-01-01 00:00:00 2022-01-02 00:00:00 1

DATE	EXPDATE	ID
2022-01-02 00:00:00	2022-01-03 00:00:00	3
2022-01-01 00:00:00	2022-01-02 00:00:00	1

db<>fiddle here