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.

http://stackoverflow.com/questions/8198962/taking-the-second-last-row-with-only-one-select-in-sql-server ...You can add a column like created date (maybe using a trigger) or an auto-increment unique key; and use that in your ORDER BY clause... — Deep Kalra, Jul 30 '15 at 06:31
There's no "second last" row in a relational database. So there's no way to get your expected result. — dnoeth, Jul 30 '15 at 06:40
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 — david sam, Jul 30 '15 at 06:42
No reliable way (if your table has no clustered index you might try a `select * from table` and scroll to the bottom) — dnoeth, Jul 30 '15 at 06:45
@davidsam - add one identity column, then you can solve your problem very easily — Fathah Rehman P, Jul 30 '15 at 06:47
In your case, there is no logical way to capture that detail. SQL Server stores and fetches data in a random fashion. Unless you tie the ordering with a column's value, there is just no reliable way to get it. What I mean is, today when you `select` out of a table, `customer id = 5` might seem like the second from bottom. Tomorrow some other id might replace this. There is just no guarantee on the order in which the results are fetched unless you use an `ORDER BY` clause — SouravA, Jul 30 '15 at 06:51
How can an ID column be random? It makes no sense, not to say it's very very bad and doesn't follow any rules of Relational DB. — CiucaS, Jul 30 '15 at 07:17
possible duplicate of [How to SELECT the last 10 rows of an SQL table which has no ID field?](http://stackoverflow.com/questions/4714975/how-to-select-the-last-10-rows-of-an-sql-table-which-has-no-id-field) — Tony, Jul 30 '15 at 07:43
@CiucaS: There's no such rule claiming an ID must be sequential but might not be random in relational theory, not even Standard SQL claims that. — dnoeth, Jul 30 '15 at 08:39

Stanislovas Kalašnikovas · Accepted Answer · 2015-07-30T06:55:16.327

3

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

edited Jul 30 '15 at 06:55

answered Jul 30 '15 at 06:29

Stanislovas Kalašnikovas

9,203
4
32
59

3

Your answer is also "wrong", there's no way to get the expected result based on the existing data. – dnoeth Jul 30 '15 at 06:42
You complaint on PKirby's answer being wrong. Btw, ROW_NUMBER would be a better approach, but still return a wrong result – dnoeth Jul 30 '15 at 06:43
`ROW_NUMBER` will be wrong too till OP not create any additional column for sorting. – Stanislovas Kalašnikovas Jul 30 '15 at 06:45
That's why I said "no way to get the expected result" :-) – dnoeth Jul 30 '15 at 06:46
but if you sort the apples here you ll miss the required apple :) – Tharif Jul 30 '15 at 07:12

score 2 · Answer 2 · answered Jul 30 '15 at 07:36

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

answered Jul 30 '15 at 07:36

t-clausen.dk

43,517
12
59
92

This is probably the best way, because it does not use `TOP`. The query (or subquery) that contains `TOP` keyword cannot be executed using parallelisation. – Serge Feb 01 '17 at 14:06

score 1 · Answer 3 · answered Jul 30 '15 at 06:53

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

score 1 · Answer 4 · answered Jul 30 '15 at 06:55

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.

score 0 · Answer 5 · answered Jul 30 '15 at 07:31

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 :

score 0 · Answer 6 · answered Nov 14 '17 at 13:09

0

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)

answered Nov 14 '17 at 13:09

Przemyslaw Remin

6,276
25
113
191

score 0 · Answer 7 · answered Feb 23 '18 at 12:03

0

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

answered Feb 23 '18 at 12:03

Nilesh Umaretiya

35
6

score -1 · Answer 8 · answered Aug 01 '15 at 10:43

-1

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

answered Aug 01 '15 at 10:43

gige

322
3
7

OP said the id field is random, so it is of no use to order the data. – Tony Aug 02 '15 at 21:32

How can I retrieve second last row?

8 Answers8

Linked