2

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!

NullUserException
  • 83,810
  • 28
  • 209
  • 234
vulkanino
  • 9,074
  • 7
  • 44
  • 71

5 Answers5

4
select id, descr
from t
order by 
 case when id <= 4 then 0 else 1 end, 
 descr
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • +1 I like this, my first thought for stuff like this is always UNION instead of thinking of the slicker solution. – heisenberg Aug 05 '10 at 15:10
  • Why not just `ORDER BY id <= 4 DESC, descr`? ... The comparison operators already return `0` or `1`. – Daniel Vassallo Aug 05 '10 at 15:13
  • @Daniel: Because initially OP didn't say anything about the database. This trick might be valid in mysql (I believe it's an extension?), but, for instance, in MS SQL Server the result of a comparison is a boolean value which cannot be used this way, therefore a `case` is a must. – GSerg Aug 05 '10 at 17:04
  • Oh, you're right. I see that the `[mysql]` tag was added later. Yes it won't work in SQL Server, but in MySQL, the `ORDER BY` syntax allows any valid expression... Anyway +1 as yours is more portable. – Daniel Vassallo Aug 05 '10 at 17:21
2
select *, id<=4 as low from table order by low, description
Scott Saunders
  • 29,840
  • 14
  • 57
  • 64
1

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:

Community
  • 1
  • 1
Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
1
select id, description
from MyTable
order by case when id <= 4 then 0 else 1 end, description
D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
1

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
a1ex07
  • 36,826
  • 12
  • 90
  • 103