Geek asks and answers

What is the best way to find the “next but newest” date in SQL?

I have a table on SQL Server that contains data organized by BusinessDataDate. BusinessDataDate is only a date (no time) and is only filled on business days. for example, not weekends or holidays.

Since there is no specific order for this, I want to find date before the current max date

This query works, and I can set the values ​​to local variables - but there seems to be a cleaner way?

SELECT MAX(BusinessDataDate) FROM myTable 
  WHERE BusinessDataDate < (SELECT MAX(BusinessDataDate) FROM myTable)

Each date will have several rows in the table - the exact number is not predictable.

+5
source share
2 answers

Like in the original query, but how to use top 1?

SELECT Top 1 BusinessDataDate 
FROM myTable 
WHERE BusinessDataDate < (SELECT MAX(BusinessDataDate) FROM myTable)
ORDER BY BusinessDataDate DESC
+2
source 
SELECT TOP (1)
    BusinessDataDate
FROM
    (
    SELECT
        BusinessDataDate,
        DENSE_RANK() OVER (ORDER BY BusinessDataDate DESC) as rn
    FROM
        myTable
    ) x
WHERE
    x.rn = 2
ORDER BY
    x.BusinessDataDate;
+3
source

More articles:


All Articles