Page 1 of 2
Strange ORDER BY! A Very Interesting Question
Posted: Thu Jul 26, 2007 12:50 pm
by Behzad
Hi,
Suppose you have a table named "Numbers",
with only one column "id", which contains the following rows:
How can we retrieve the following result in MySQL:
Code: Select all
10
9
8
7
6
..
1
11
12
13
14
...
50
As you see, half of the result is sorted in descending order,
whereas the other half is sorted in ascending order.
Posted: Thu Jul 26, 2007 3:38 pm
by miro_igov
I don't see any logic in the needed result order. Can you explain it?
Posted: Thu Jul 26, 2007 3:54 pm
by Behzad
Sure.
The actual rows in the table:
1,2,3,4,5,6,7,8,9,10,11,12,13,14,...,50
But the requested query consists of rows 10~1 in descending order
and rows 11~50 in ascending order.
and this is the requested order:
10,9,8,7,6,5,4,3,2,1,11,12,13,14,...50
Is this possible?
Posted: Thu Jul 26, 2007 3:58 pm
by miro_igov
If you don't use ORDER BY, yes this is possible. I seen in my databases how SELECT with no specified ORDER BY selects the record IDs (primary key) in very strange order, i can't explain it.
Posted: Thu Jul 26, 2007 4:28 pm
by feyd
Might need to be a union, although conditional clauses may be supported in order by..
Posted: Thu Jul 26, 2007 4:40 pm
by Behzad
This is not an easy question. I actually tried UNION before posting the first message.
But it failed.
Code: 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
...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.
@see:
http://dev.mysql.com/doc/refman/4.1/en/union.html
Any other solution(s)?
Posted: Fri Jul 27, 2007 4:00 am
by onion2k
If you really want to get 1 through 10 in descending order then this is the easy way to do it...
Code: Select all
SELECT `myTable`. * ,
IF( `myTable`.`id` < 11, `myTable`.`id` , 0 ) AS descOrderValue
FROM `myTable`
WHERE 1
ORDER BY descOrderValue DESC , `myTable`.`id` ASC
If you actually want the first 10 results
regardless of their id descending then it's not quite as easy.
Posted: Fri Jul 27, 2007 4:27 am
by timvw
How about something as following:
Code: Select all
SELECT *, @OrderId := IF(ID < 10, 10 - ID, ID)
FROM foo
ORDER BY @OrderId ASC;
Posted: Fri Jul 27, 2007 4:41 am
by Behzad
Thanks. After a little modifications, I found out the answer:
Code: Select all
SELECT * FROM numbers
ORDER BY (IF(ID <= 10, 10 - ID, ID)) ASC;
But is there a way to sort 50% of the result in descending order and 50% is ascending order?
Posted: Fri Jul 27, 2007 4:51 am
by onion2k
Cracked it ... ordered as per the request, even if the IDs aren't 1 through 10.
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` ASC
Posted: Fri Jul 27, 2007 5:02 am
by Behzad
ٌٌٌٌٌٌٌWWWWWWWWWWWWOOWW!
VERY INTERESTING!
Posted: Fri Jul 27, 2007 9:31 am
by Behzad
َIt seems the last query does not work when we fill in the table with duplicate entries
or when the inserted rows are not inserted with a logical order.
Code: 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);
But this one works:
Code: Select all
SELECT * FROM numbers
ORDER BY (IF(ID <= 10, 10 - ID, ID)) ASC;
Posted: Fri Jul 27, 2007 9:38 am
by onion2k
If the id field is an auto_increment then how are you getting duplicates or ids in the wrong order? If that's happening then you have a very serious problem in your code.
Posted: Fri Jul 27, 2007 9:51 am
by Behzad
Oops. I'm sorry.
I edited the my previous post.
But the question still remains.
Posted: Fri Jul 27, 2007 10:02 am
by superdezign
onion2k's code should work fine. It works using the primary key.