CODEWITHSUNDEEP
sqlsql-servert-sql
BigIWT
BigIWT

Reputation: 293

Query to find rows with nearest date in future

I'm trying to display a result set based on a min date value and today's date but can't seem to make it work. It's essentially a date sensitive price list.

Example Data

ID    Title    Value    ExpireDate
1     Fred      10       2019-03-01
2     Barney    15       2019-03-01
3     Fred2     20       2019-06-01
4     Barney2   25       2019-06-01
5     Fred3     30       2019-07-01
6     Barney3   55       2019-07-01

Required Results:
Display records based on minimum date > GetDate()

3     Fred2     20       2019-06-01
4     Barney2   25       2019-06-01

Any assistance would be great - thank you.

Upvotes: 0

Views: 595

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272106

Use where clause to filter all future rows and row_number() to find the first row per group:

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Title ORDER BY ExpireDate) AS rn
    FROM t
    WHERE ExpireDate >= CAST(CURRENT_TIMESTAMP AS DATE)
) AS x
WHERE rn = 1

Based on your revised question, you can simply do this:

SELECT TOP 1 WITH TIES *
FROM t
WHERE ExpireDate >= CAST(CURRENT_TIMESTAMP AS DATE)
ORDER BY ExpireDate

Upvotes: 4

Related Questions