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.

Answer 2

"select TOP (1) *   from Table     WHERE Date<(SELECT MAX(Date) FROM Table)   ORDER BY Date DESC" 

should do the trick.

Answer 3

Please check this code.

SELECT * FROM category 
WHERE Created_Time <(
                     SELECT MAX(Created_Time) 
                     FROM category
                     ) 
ORDER BY Created_Time DESC 
LIMIT 1

Prasad.

Answer 4

select second last date in sql:

 SELECT MAX(YourDateColumn) FROM YourTable where ColumnId=2 and  YourDateColumn <
(SELECT MAX(YourDateColumn) FROM YourTable where ColumnId=2)

hope helps someone.