Get second last record of a table without using order by [Resolved]

Posted by ashsep18-21378 under Sql Server on 11/21/2014 | Points: 10 | Views : 477 | Status : [Member] | Replies : 3
How to get the second last record in a table without any identity column in SQL server 2012?

Note: Order by Clause should not be used.

Ashwini


Responses

Posted by: Bandi on: 11/22/2014 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
declare @count int 
SELECT @count = count(*) from tableName

select * from tableName
except
select top (@count - 2) * from tableName


without using any identity column and ORDER BY clause we can get last 2 records from a table by using above code

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

ashsep18-21378, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/22/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
select * from tableName where PKColumn not in 
(select top((select COUNT(*) from tableName ) -2 )PKColumn from tableName)


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

ashsep18-21378, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: ashsep18-21378 on: 11/24/2014 [Member] Starter | Points: 25

Up
0
Down
Thanks Bandi. Your first answer is what I wanted.

Ashwini

ashsep18-21378, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response