selecting all rows except top row using offset in MySQL-CodePudding

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?

CodePudding user response：

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;

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

I have chosen that limit number from a different question

CodePudding user response：

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;

CodePudding user response：

You have to use LIMIT in addition to OFFSET

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