CODEWITHSUNDEEP
sql
Asbah Ishaq
Asbah Ishaq

Reputation: 313

SQL query to get 2nd last row

I have a sql table named User. I want to sort it by id and get the second last user. Is there a generic way to do that in sql?

For example I have these rows:

id    name
1     User 1
2     User 2
3     User 3
4     User 4

I want to write a sql query to get User 3. I want a generic query that I can use for the 3rd last or 4th last user as well.

Upvotes: 0

Views: 1106

Answers (2)

ggordon
ggordon

Reputation: 10035

The query below will get you the second to last user based on the id. It achieves this by first ordering by id in descending order, using the OFFSET 1 to skip the new first row in the order i.e. User 4 and the LIMIT 1 to get just User 3 the next remaining user. The general pattern as shown in the fiddle which you can run online is to update the OFFSET

Schema (MySQL v5.7)

CREATE TABLE User (
  `id` INTEGER,
  `name` VARCHAR(6)
);

INSERT INTO User
  (`id`, `name`)
VALUES
  ('1', 'User 1'),
  ('2', 'User 2'),
  ('3', 'User 3'),
  ('4', 'User 4');

Query #1

SELECT "GET 2nd to Last";
GET 2nd to Last
GET 2nd to Last

Query #2

SELECT
    *
FROM 
   User
ORDER BY
   id desc
LIMIT 1
OFFSET 1;
id name
3 User 3

Query #3

SELECT "GET 3rd to Last";
GET 3rd to Last
GET 3rd to Last

Query #4

SELECT
    *
FROM 
   User
ORDER BY
   id desc
LIMIT 1
OFFSET 2;
id name
2 User 2

Query #5

SELECT "GET 4th to Last";
GET 4th to Last
GET 4th to Last

Query #6

SELECT
    *
FROM 
   User
ORDER BY
   id desc
LIMIT 1
OFFSET 3;
id name
1 User 1

View on DB Fiddle

Upvotes: 3

gene
gene

Reputation: 41

Alternate solution easy to understand solution:

You can replace the LIMIT 2 in the nested query with LIMIT 3 to get the 3rd to last or LIMIT 4 to get the 4th to last user.

SELECT *
FROM (
 SELECT *
 FROM User
 ORDER BY id DESC
 LIMIT 2
)
ORDER BY id
LIMIT 1

Upvotes: 2

Related Questions