selecting all rows except top row using offset in MySQL

Question

How can I use offset clause to get all rows except the first row. I am using query like

SELECT * FROM EMPLOYEES ORDER BY SALARY OFFSET 1;

But this is not working in MySQL. what am I doing wrong here?

MySQL <> SQL Server - please correct your tags. – Dale K May 12 '22 at 20:24 — Dale K, May 12 '22 at 20:24
In SQL server it would be `offset 1 rows` – Salman A May 12 '22 at 20:30 — Salman A, May 12 '22 at 20:30

score 1 · Accepted Answer · answered May 12 '22 at 20:33

Sadly in MySQL OFFSET only works together with the LIMIT clause. So you need to use

SELECT * FROM EMPLOYEES ORDER BY SALARY LIMIT 18446744073709551615 OFFSET 1;

or

SELECT * FROM EMPLOYEES ORDER BY SALARY LIMIT 1, 18446744073709551615;

I have chosen that limit number from a different question

score 1 · Answer 2 · answered May 12 '22 at 20:37

1

MySQL 8.0 can use a window function to do this:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY SALARY) AS rownum
  FROM EMPLOYEES
) AS t
WHERE rownum > 1
ORDER BY SALARY;

answered May 12 '22 at 20:37

Bill Karwin

score 0 · Answer 3 · answered May 12 '22 at 20:33

0

You have to use LIMIT in addition to OFFSET

SELECT * FROM EMPLOYEES ORDER BY SALARY LIMIT 9999 OFFSET 1;

answered May 12 '22 at 20:33

Chris B

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 14 '22 at 01:23

3 Answers3