How to get the second last record in sql

Question

Hi i have below table and the records are ATN_No ATN_Number ATN_Date ATN_AstNo 1 A-1 01-01-2016 1 2 A-1 01-01-2016 2 3 A-2 02-01-2016 1 4 A-3 02-01-2016 2 i want below result depend upon ATN_Date and ATN_AstNo. ATN_No ATN_Number ATN_Date ATN_AstNo 1 A-1 01-01-2016 1 2 A-1 01-01-2016 2 Thanks Basit.

Rafnas T P · Answer

select top 1 ( SELECT top 2 * FROM TableName WHERE ATN_Date = '2016-01-01' AND ATN_AstNo IN (12) order by ATN_AstNo desc )

Rathrola Prem Kumar · Answer

select * from emp where empid not in ( select top ( ( select count(*) from emp) - 2 ) empid from emp ) Refer to this example

Catcher Wong · Answer

Hi Basit  depend upon ATN_Date and ATN_AstNo  what do you mean ? If this code can solve your question? SELECT * FROM dbo.Test t WHERE t.ATN_Date = '2016-01-01' AND t.ATN_AstNo IN (12);

Ramesh Barik · Answer

;WITH QResult AS (SELECT ROW_NUMBER() OVER ( PARTITION BY ATN_Date ATN_AstNo ORDER BY ATN_Date DESC ) AS Row ATN_No ATN_Number ATN_Date ATN_AstNo FROM tblName) SELECT * FROM QResult WHERE row = 2