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

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

Answer 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