Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005 SQL-Server , +
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 
Posted 17-Nov-11 19:50pm
Edited 17-Nov-11 20:33pm
v4
Comments
Mehdi Gholam at 18-Nov-11 1:28am
   
What's wrong with using order by, some things are there to be used.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

select * 
from emp
where empid not in (
    select top (
        (select count(*) from emp) - 2
    ) empid
    from emp
)
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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.
  Permalink  
Comments
kiran dangar at 18-Nov-11 1:34am
   
but Not having Identity
PMK2429 at 10-May-13 15:19pm
   
Hello Kiran,
This is Pavitra Kansara.
I want to learn MVC in .NET but I find it difficult to start with.
Will you please help me out?
if possible please revert me on pavitra_mukrash@yahoo.com
 
Regards,
Pavitra M Kansara.
Ahmedabad.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

make use of max() in sql .have look at at the following query:
select * from emp where id=(select max(id)-1 from emp)
  Permalink  
Comments
Janardhanam Julapalli at 21-Oct-14 5:59am
   
This is working fine.@member60

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,142
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 18 Nov 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100