How can I retrieve second last row?

Question

I have a table with many records and I want to know only the record which I have created at second last.

For ex: I have a table customer in which customerID are random numbers.

Now I want to select second last row.

customerID      customer_name   cont_no
---------------------------------------
 7              david sam       5284
 1              shinthol        1
11              lava            12548
 2              thomas          1
 3              peeter          1
 4              magge           1
 5              revas           1
 6              leela           123975

Output row :

customerID      customer_name   cont_no
5               revas           1

I don't want second highest...

I want second last row.

Answer 1

Try this

;WITH tbl_rn AS (
    select 
        RowNum = row_number() OVER (ORDER BY @@rowcount),
        customerID,
        customer_name,
        cont_no
    from  tbl
)
select 
    customerID,
    customer_name,
    cont_no
from tbl_rn 
where RowNum = (select max(RowNum) - 1 from tbl_rn)

Here RowNum is a column by numbering the rows in the table with out ordering it.

max(RowNum) - 1 will give the second last

Answer 2

Posting as an answer as it is a big comment

David: ok i will do it next time but what i can do now for this problem there are many recods in thousand.is there any way to do this?? @Deepanshu Kalara

Me: @david sam, I dont think there is a way to do this now. Best bet would be copy those thousand records in excel and hope that they are in order you inserted them. Create a manual column there like you would have had if you had auto-increment. and correct your table structure by inserting that column in the table itself, as you said you would.

Answer 3

i know this is too late but you can try this.

SELECT TOP 1 * FROM (SELECT * FROM dbo.customer
EXCEPT SELECT TOP (SELECT (COUNT(*)-2) FROM dbo.customer ) * FROM dbo.customer) A 

Answer 4

Datas should be sorted before they can be effectively search.

I would recommend to add an extra field in your table id with autoincrement.

Its not a big deal as below :

Query :

SELECT        TOP (1) customerID, customer_name, cont_no, id
FROM            (SELECT        TOP (2) customerID, customer_name, cont_no, id
                          FROM            customer
                          ORDER BY id DESC) AS t
ORDER BY id

First top 2 Data is selected in a descending (DESC) manner where you get results based on id value as :

8,7 (8 values are available in example shown)

Next select the top 1 value in ASC (ascending manner)

Output :

Answer 5

select identity(int,1,1) as Id, * into #temp from customer
select * from #temp where Id = (select max(Id) as count from #temp group by Id) - 1 drop table #temp

Answer 6

With SQL Server 2012 or higher you can do it in one line code:

LAG([MyValue],1) OVER (PARTITION BY [Category] ORDER BY [AnyColumnForOrdinal] ASC)