Binaryworld - Getting the second recent date from a set of values. ... [ SQL Server -> T-SQL ]

ANSI & T-SQL specific solutions for getting the 2nd recent date from a set of values. This can be extended to answer nth date from a set of values but the ANSI version gets unwieldy & has to be modified for bigger values.

Click here to copy the following block

-- ANSI SQL version:
SELECT MIN( t1.updatedate ) AS "2ndRecentDate"
FROM sysusers AS t1
WHERE t1.updatedate = ( SELECT MAX( t2.updatedate )
            FROM sysusers AS t2
            WHERE t2.updatedate <
               ( SELECT MAX( t3.updatedate )
                FROM sysusers AS t3 )
           )
/*
2ndRecentDate
------------------------------------------------------
1998-11-13 02:58:21.233
*/
GO
-- SQL70/2000 version:
SELECT TOP 1 t.updatedate AS "2ndRecentDate"
FROM (
  SELECT TOP 2 updatedate
  FROM sysusers
  ORDER BY updatedate DESC
) AS t
ORDER BY t.updatedate
/*
2ndRecentDate
------------------------------------------------------
1998-11-13 02:58:21.233
*/
GO