如何获取SQL中的第二个最后一条记录 [英] How to get the second last record in SQL

问题描述

Hi,

i have below table and the records are

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)

what was the last ATN_Date and ATN_AstNo.

below is the result what i want.

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

Thanks
Basit.

我尝试过：



i尝试以下查询

What I have tried:

i tried below query

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
)

然后结果来了

then result came

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

推荐答案

尝试：

SELECT ATN_No, ATN_Date, ATN_AstNo
FROM (SELECT ATN_No, ATN_Date, ATN_AstNo, 
             ROW_NUMBER() OVER (ORDER BY ATN_Date,ATN_AstNo DES) AS RowNum
      FROM dbo.Trn_ATN
     ) AS MyDerivedTable
WHERE MyDerivedTable.RowNum = 2

这篇关于如何获取SQL中的第二个最后一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！