How can I get the second last line?
I have a table with many records and I only want to know the record I created the second time.
For example: I have a table customer
that contains customerID
random numbers.
Now I want to select the second last line.
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 line:
customerID customer_name cont_no
5 revas 1
I don't want the second tallest ...
I want the second last line.
source to share
As you asked, I can give you an example.
Imagine you have a full bag of apples. How can you take the second last apple? How do you know which one is the second? You cannot do this until you sort them.
Your data is not sorted yet, so you cannot achieve this as expected. You can do this in the following, only after you have any sorting criteria such as Id
, date created
or so on.
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
source to share
Posting as an answer as this is a great comment
David: Ok, I'll do it next time, but what can I do now for this problem, there are many recommendations in the thousands. Is there a way to do this? @Deepanshu Kalara
Me: @david sam, I don't think there is a way to do this now. Your best bet would be to copy those thousands of records into Excel and hope they are okay for you to paste. Create the column manually as if you had auto increment. and fix your table structure by inserting that column into the table as you said you would.
source to share
As you probably already know, to complete this task you need an ordering column. OVER is 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
source to share
try it
;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 the column by numbering the rows in the table without ordering it.
max(RowNum) - 1
will give the second last
source to share
The data must be sorted before it can be searched effectively.
I would recommend adding an extra field to your table id using autoincrement
.
It doesn't really matter as shown below:
Request:
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 vertex 2 Data is fetched in descending order (DESC) where you get the results based on the id value as:
8.7 (8 values are available in the example shown)
Then select the top 1 in ASC (Ascending)
Output:
source to share