How can I retrieve second last row?

问题:

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.

回答1:

As you asked I can give you example.

Imagine, that you have full bag of apples. How can you take second last apple? How you will know which one is second last? You can't do It while you not sort them in any way.

---

For now your data isn't sorted so you can't achieve It as expected. You can do It in following, only after you have any sorting criteria like Id, date created or etc.

SELECT TOP 1 * 
FROM(
    SELECT TOP 2 * 
    FROM Tbl 
    ORDER BY SortingCol DESC -- here you need to pass column which will provide expected sorting
    ) t                     
ORDER BY SortingCol

回答2:

As you probably already know, you need a column to order by to achieve this task. OVER Clause be used for this.

;WITH CTE as
(
  SELECT 
    customerid, customer_name, cont_no, 
    row_number() over (order by newlymadesortcolumn desc) rn
  FROM customer
)
SELECT customerid, customer_name, cont_no
FROM CTE
WHERE rn = 2

回答3:

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

回答4:

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.

回答5:

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 :

回答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)

回答7:

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 

回答8:

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