Select row by number in MySQL-CodePudding

I want to select all fields from a row of a table matching a particular row number. This sounds simple, but I can't figure out the syntax from the online doc. (I don't want to count rows or use variables to count the roles myself). Something like this should work:

select * from mytable where row_number()=2

The simplest examples I can find (eg: enter link description here) use OVER clause, so I tried something like:

SELECT 
    ROW_NUMBER() OVER (
        ORDER BY REFNUM
    ) *
FROM 
    MYTABLE
WHERE
    ROW_NUMBER() =2

But still not valid syntax.

CodePudding user response：

You need to assign an alias to the ROW_NUMBER() expression, then test that.

SELECT ROW_NUMBER() OVER (ORDER BY REFNUM) AS row, *
FROM YourTable
HAVING row = 2

This is essentially the same as

SELECT *
FROM YourTable
ORDER BY REFNUM
LIMIT 1, 1

LIMIT starts counting from 0, while ROW_NUMBER() starts at 1, so the offset in the LIMIT is 1 less.

CodePudding user response：

The simplest way to achieve the same behavior is by using limit

SELECT * FROM table LIMIT 1, 1