SQL Query conditional on date column [duplicate]

Question

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 group by? 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

Answer 1

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;

For SQL Server, the equivalent would be:

SELECT "DATE", ExpDate, ID
FROM   (
  SELECT t.*,
         RANK() OVER (PARTITION BY "DATE" ORDER BY DATEDIFF(dd, Date, ExpDate))
           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

Oracle db<>fiddle SQL Server db<>fiddle