How to get the second last record in sql

Transact-SQL
1

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.

below is the result of second last record, what will be the query.

ATN_No, ATN_Number, ATN_Date ,ATN_AstNo
1                 A-1                 01-01-2016       1
2                 A-1                 01-01-2016       2

Thanks
Basit.

2

Something like this..... At least you can start with below query.

CREATE TABLE TestTable
(
[Attn_No] INT
,[ATN_Number] VARCHAR(10)
,[ATN_Date] VARCHAR(15)
,[ATN_AstNo] INT

)

INSERT INTO dbo.TestTable
( Attn_No ,
ATN_Number ,
ATN_Date ,
ATN_AstNo
)
SELECT '1','A-1','01-01-2016','1'
UNION ALL
SELECT '2','A-1','01-01-2016','2'
UNION ALL
SELECT '3','A-2','02-01-2016','1'
UNION ALL
SELECT '4','A-3','02-01-2016','2'

SELECT * FROM dbo.TestTable

SELECT
TOP 2
* FROM dbo.TestTable
WHERE EXISTS (
SELECT
TOP 2
Attn_No
,Attn_No
,ATN_Date
,ATN_AstNo
FROM
dbo.TestTable
ORDER BY ATN_Date,ATN_AstNo DESC
)

ORDER BY  ATN_Date,ATN_AstNo
3

Thanks a lot. I tried this before. but if the data is below.

CREATE TABLE [dbo].[Trn_ATN](
	[ATN_No] [numeric](18, 0) NULL,
	[ATN_Number] [nvarchar](50) NULL,
	[ATN_Date] [date] NULL,
	[ATN_AstNo] [numeric](18, 0) NULL
) ON [PRIMARY]

GO

datas are

insert into Trn_ATN (ATN_No,ATN_Number,ATN_Date,ATN_AstNo) values(1,'A-1','2016-01-01',1)
insert into Trn_ATN (ATN_No,ATN_Number,ATN_Date,ATN_AstNo) values(2,'A-1','2016-01-01',2)
insert into Trn_ATN (ATN_No,ATN_Number,ATN_Date,ATN_AstNo) values(3,'A-2','2016-01-02',1)
insert into Trn_ATN (ATN_No,ATN_Number,ATN_Date,ATN_AstNo) values(4,'A-4','2016-01-02',2)
insert into Trn_ATN (ATN_No,ATN_Number,ATN_Date,ATN_AstNo) values(5,'A-3','2016-01-03',1)

then the result is

ATN_No	ATN_Number	ATN_Date	ATN_AstNo
1	A-1	         1/1/2016	       1
4	A-2	         1/2/2016	       2

Thanks
Basit.

4

For some reason, I am getting a different result than you. Here is I am getting after latest data

5

Yes with below query result is coming as you sent.

SELECT
TOP 2
* FROM dbo.Trn_ATN
WHERE EXISTS (
SELECT
TOP 2
ATN_No
,ATN_Date
,ATN_AstNo
FROM 
dbo.Trn_ATN
ORDER BY ATN_Date,ATN_AstNo DESC
)

but i want above result.

ATN_No	ATN_Number	ATN_Date	ATN_AstNo
2	     A-1	   1/1/2016	       2
3	     A-2	   1/2/2016	       1

what was the last ATN_Date and ATN_AstNo.

Thanks
Basit.

6

Is there any update?

7

Below query solved my problem.

SELECT *
FROM Trn_ATN a
WHERE
  2 = (SELECT count(DISTINCT(b.ATN_Date))
       FROM Trn_ATN b WHERE
       a.ATN_Date <= b.ATN_Date and a.ATN_AstNo=b.ATN_AstNo)