Suppose you have a table named "Numbers",
with only one column "id", which contains the following rows:
Code: Select all
1
2
3
4
...
50Code: Select all
10
9
8
7
6
..
1
11
12
13
14
...
50whereas the other half is sorted in ascending order.
Moderator: General Moderators
Code: Select all
1
2
3
4
...
50Code: Select all
10
9
8
7
6
..
1
11
12
13
14
...
50Code: Select all
SELECT R.id FROM (
(
SELECT id, 1 AS sort_col
FROM numbers
WHERE id <= 10
ORDER BY id DESC
)
UNION
(
SELECT id, 2 AS sort_col
FROM numbers
WHERE id > 10
ORDER BY id ASC
)
ORDER BY
sort_col ASC
) R@see: http://dev.mysql.com/doc/refman/4.1/en/union.html...Use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. If ORDER BY appears with LIMIT, it is used to determine the subset of the selected rows to retrieve for the SELECT, but does not necessarily affect the order of those rows in the final UNION result.
Code: Select all
SELECT `myTable`. * ,
IF( `myTable`.`id` < 11, `myTable`.`id` , 0 ) AS descOrderValue
FROM `myTable`
WHERE 1
ORDER BY descOrderValue DESC , `myTable`.`id` ASCCode: Select all
SELECT *, @OrderId := IF(ID < 10, 10 - ID, ID)
FROM foo
ORDER BY @OrderId ASC;Code: Select all
SELECT * FROM numbers
ORDER BY (IF(ID <= 10, 10 - ID, ID)) ASC;Code: Select all
SELECT `myTable`.*,
@counter:=@counter+1 AS countValue,
IF (@counter<=10, item_id, 0) as descOrder
FROM `myTable`, (SELECT @counter:=0) c
WHERE 1
ORDER BY descOrder DESC, `myTable`.`id` ASCCode: Select all
CREATE TABLE `numbers` (
`id` int(10) unsigned
) ENGINE=MyISAM;
INSERT INTO `numbers` (`id`) VALUES
(11),(20),(3),(4),(25),(6),(7),(8),(9),(1),
(10),(12),(13),(14),(15),(17),(18),(19),(2),
(31),(22),(24),(26),(27),(28),(29),(30),(21),
(32),(33),(34),(35),(36),(37),(38),(39),(40),
(41),(42),(43),(44),(45),(46),(47),(48),(49),
(50),(6),(6);Code: Select all
SELECT * FROM numbers
ORDER BY (IF(ID <= 10, 10 - ID, ID)) ASC;