如何在SQL中获取表的倒数第二行而不使用“ORDER BY”

本文介绍了如何在SQL中获取表的倒数第二行而不使用“ORDER BY”的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在接受采访时被问到一个问题：

I have been asked a question in an interview :

How to get second last record of a SQL table ?...WITHOUT USING "ORDER BY" , and assuming there is no identity column

推荐答案

嗨kiran， 如果您在表格中使用身份，则以下查询将非常有用， Hi kiran, If you are using an identity in your table , following query will be useful, select * from gallery where GalleryItemid=(ident_current('gallery')-1)

我希望它可以帮助你。

I hope it helps you.

我找到了一个解决方案，但是有一个小的限制，但它可以在99％的情况下工作.. i found a solution but there is a small limitation, however it will work in 99% cases.. select top 1* from ( select * from [TableName] EXCEPT select top (select (COUNT(1)-2) from [TableName]) * from [TableName]) A

限制：如果最后一行/秒的最后一行是任何行的副本，那么它将给出错误的结果......但在实际情况下这种情况非常罕见。

Limitation : If last 2 rows/ second last row is duplicate of any row then it will give wrong result... but such cases are very rare in real scenarios.

select * from emp where empid not in ( select top ( (select count(*) from emp) - 2 ) empid from emp )