double sorted selection from a single table

Question

I have a table with an id as the primary key, and a description as another field.

I want to first select the records that have the id<=4, sorted by description, then I want all the other records (id>4), sorted by description. Can't get there!

Answer 1

select *, id<=4 as low from table order by low, description

Answer 2

You may want to use an id <= 4 expression in your ORDER BY clause:

SELECT * FROM your_table ORDER BY id <= 4 DESC, description;

Test case (using MySQL):

CREATE TABLE your_table (id int, description varchar(50));

INSERT INTO your_table VALUES (1, 'c');
INSERT INTO your_table VALUES (2, 'a');
INSERT INTO your_table VALUES (3, 'z');
INSERT INTO your_table VALUES (4, 'b');
INSERT INTO your_table VALUES (5, 'g');
INSERT INTO your_table VALUES (6, 'o');
INSERT INTO your_table VALUES (7, 'c');
INSERT INTO your_table VALUES (8, 'p');

Result:

+------+-------------+
| id   | description |
+------+-------------+
|    2 | a           |
|    4 | b           |
|    1 | c           |
|    3 | z           |
|    7 | c           |
|    5 | g           |
|    6 | o           |
|    8 | p           |
+------+-------------+
8 rows in set (0.00 sec)

---

Related post:

• Using MySql, can I sort a column but have 0 come last?

Answer 3

select id, descr
from t
order by 
 case when id <= 4 then 0 else 1 end, 
 descr

Answer 4

select id, description
from MyTable
order by case when id <= 4 then 0 else 1 end, description

Answer 5

You can use UNION

SELECT * FROM (SELECT * FROM table1 WHERE id <=4 ORDER by description)aaa
UNION
SELECT * FROM (SELECT * FROM table1 WHERE id >4 ORDER by description)bbb

OR

SELECT * FROM table1
ORDER BY
CASE WHEN id <=4 THEN 0
ELSE 1
END, description