Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Whats the best way to find the "next but one newest" date in SQL?

I have a table in SQL Server that holds data organised by BusinessDataDate. BusinessDataDate is a Date only (no time component) and is only populated on Business days. e.g. not Weekends or public holidays.

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

This query works, and I can set the values into local variables - but it feels like there must be a cleaner way?

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

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

like image 405
BlueChippy Avatar asked Apr 22 '13 11:04

BlueChippy


1 Answers

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;
like image 61
gbn Avatar answered Sep 27 '22 22:09

gbn