如何在SQL中获取表的倒数第二行而不使用“ORDER BY” [英] How to get second last row of a table in SQL WITHOUT USING "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
)

这篇关于如何在SQL中获取表的倒数第二行而不使用“ORDER BY”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！