Menu
I am looking to return the 2nd, or 3rd, or 4th record from a MySQL query (based on a query by ID ascending)
The problem being, I won't know the ID, only that it is the 3rd row in the query.
367
mysql> select * from table_name where table_name.id mod n = 0; In the above query, we basically select every row whose id mod n value evaluates to zero. So we get rows with id n, 2n, 3n, … Replace table_name with your table name, and n with every nth row that you want to return.
ROW_NUMBER (Window Function) ROW_NUMBER (Window Function) is a standard way of selecting the nth row of a table. It is supported by all the major databases like MySQL, SQL Server, Oracle, PostgreSQL, SQLite, etc.
For SQL Server, a generic way to go by row number is as such: SET ROWCOUNT @row --@row = the row number you wish to work on.
SELECT DISTINCT(column_name) FROM table_name ORDER BY column_name DESC limit N-1,1; where N represents the nth highest salary ..
SELECT * FROM table ORDER BY ID LIMIT n-1,1
It says return one record starting at record n.
158
The accepted answer was wrong by 1 before the edit, because the offset is zero-indexed.
From the doc:
With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):
SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
So the correct query would be
SELECT * FROM table ORDER BY ID LIMIT n-1,1 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
