SQL Query conditional on date column

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

Since SQL includes data definition, a [mcve] for an [SQL question](//meta.stackoverflow.com/q/333952/90527) should include [DDL](//en.wikipedia.org/wiki/Data_definition_language) statements for sample tables (rather than an ad hoc table specification) and [DML](//en.wikipedia.org/wiki/Data_manipulation_language) statements for sample data (rather than a dump or ad hoc format). Desired results don't need to be presented as sample code, as results are the output of code and not code themselves. — outis, May 10 '22 at 21:01
Duplicate of many of the [groupwise-maximum](https://stackoverflow.com/questions/tagged/groupwise-maximum) questions. — outis, May 10 '22 at 21:03
Does this answer your question? "[Get the latest records per Group By SQL](//stackoverflow.com/q/35219261/90527)," "[How to find the record in a table that contains the maximum value?](//stackoverflow.com/q/376518/90527)" — outis, May 10 '22 at 21:32
Yep, I think this is what I was looking for. The answers below seem to be using a similar approach. Makes sense to me. — Niccola Tartaglia, May 10 '22 at 21:44

MT0 · Answer 1 · 2022-05-10T21:49:44.240

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

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

SQL Query conditional on date column

1 Answers1