SELECT COUNT(*) AS cnt,
id,
MAX(CASE WHEN Rn=1 THEN entryid END) AS Latest,
MAX(CASE WHEN Rn=2 THEN entryid END) AS NextLatest
FROM
(SELECT *,ROW_NUMBER() OVER (PARTITION BY id ORDER BY entry_Id DESC) AS Rn
FROM table
)t
GROUP BY id
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Deriving counts and maximum dates
Posted - 03/28/2012 : 16:22:36
