Return from first row if last row reached

user_2334352 0
Viewed 249

Table has an id column with rows 1 to 10. I want:

SELECT id FROM `table` WHERE id > 8 ORDER BY id ASC LIMIT 6

to return:

9 10 1 2 3 4

What's the appropriate query for this?

mysql sql
4 Answers
SELECT id
FROM `table`
ORDER BY id <= 8, id
LIMIT 6

id <= 8 will be false (0) for 9 and higher, true (1) for values lower than 9, so the higher values will be first. Then within each of those groups it orders by id.

user_14918950

This should work for any sql-like syntax

SELECT id
FROM `table`
ORDER BY case when id > 8 then 1 else 2 end, id
LIMIT 6
user_111868750

A variation that uses ROW_NUMBER:

SELECT *,  ROW_NUMBER() OVER(ORDER BY CASE WHEN id > 8 THEN 0 ELSE 1 END, id) AS rn
FROM `table`
ORDER BY rn LIMIT 6;

db<>fiddle demo

user_50708790

Try this:

SELECT * FROM (
  SELECT id FROM table WHERE id > 8 ORDER BY id
  UNION
  SELECT id FROM table WHERE id <= 8 ORDER BY id
) temp LIMIT 6;
user_37140340
Related