How to return second newest record in SQL?

Question

If this:

SELECT *
FROM Table
WHERE Date=( SELECT MAX(Date)
             FROM Table
           )

returns newest record from the table, how to get second newest record?

Answer 1

SELECT * 
FROM Table
WHERE Date = ( SELECT MAX(Date) 
               FROM Table
               WHERE Date < ( SELECT MAX(Date) 
                              FROM Table
                            )
             ) ;

or:

SELECT TOP (1) * 
FROM Table
WHERE Date < ( SELECT MAX(Date) 
               FROM Table
             ) 
ORDER BY Date DESC ;

or:

SELECT *
FROM
  ( SELECT t.*
         , ROW_NUMBER() OVER(ORDER BY Date DESC) AS RowNumber
    FROM Table t
  ) AS tmp
WHERE RowNumber = 2 ;

---

If the Date column has unique values, all three queries will give the same result. If the column can have duplicate dates, then they may give different results (when there are ties in 1st or 2nd place). The first query will even give multiple rows in the result if there are ties in 2nd place.