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 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 6

  Permalink  
Comments
Maciej Los at 4 days ago
   
It's really old question. Why did you answered it?
Kornfeld Eliyahu Peter at 4 days ago
   
Because of solution 5!
It made the question jump to the top of the list...
/\jmot at 4 days ago
   
it show on the top, that's why i answered.
Maciej Los at 4 days ago
   
So, delete it to avoid down-voting.
/\jmot at 4 days ago
   
why down vote?
is that a wrong answer or anything else??
i saw the question on top and answered it.if you thing my answer is wrong or you didn't like this way then down vote it.
it's not the issue. :)
 
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 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 240
1 Kamal Rocks 184
2 PIEBALDconsult 150
3 BillWoodruff 148
4 Jochen Arndt 135
0 OriginalGriff 5,695
1 DamithSL 4,506
2 Maciej Los 4,007
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,190


Advertise | Privacy | Mobile
Web01 | 2.8.141216.1 | Last Updated 15 Dec 2014
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