Accessing the n row from the right or left of the x row-CodePudding

I'm trying to select the data from the left of a row. Lets assume this table:

create table user (
   ID INT PRIMARY KEY AUTO_INCREMENT
);

The rows of this table look like that:

What I want to do, is to select data from the row which has ID = 15, to the 4th row from left of it, which has ID 3, how can I do that, without using BETWEEN in query.

CodePudding user response：

What I want to do, is to select data from the row which has ID = 15, to the 4th row from left of it:

SELECT *
FROM tablename
WHERE id < 15
ORDER BY id DESC 
LIMIT 4

If you need the 4th row only then use above query as a subquery, and in outer query use backward sorting LIMIT 1.

If MySQL version is 8 then use ROW_NUMBER() in CTE.