Strange ORDER BY! A Very Interesting Question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Behzad
Forum Commoner
Posts: 28
Joined: Mon Jul 09, 2007 3:24 pm
Location: Tehran, Iran
Contact:

Strange ORDER BY! A Very Interesting Question

Post by Behzad »

Hi,

Suppose you have a table named "Numbers",
with only one column "id", which contains the following rows:

Code: Select all

1
2
3
4
...
50
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.
Last edited by Behzad on Fri Jul 27, 2007 4:56 am, edited 1 time in total.
miro_igov
Forum Contributor
Posts: 485
Joined: Fri Mar 31, 2006 5:06 am
Location: Bulgaria

Post by miro_igov »

I don't see any logic in the needed result order. Can you explain it?
Behzad
Forum Commoner
Posts: 28
Joined: Mon Jul 09, 2007 3:24 pm
Location: Tehran, Iran
Contact:

Post 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?
miro_igov
Forum Contributor
Posts: 485
Joined: Fri Mar 31, 2006 5:06 am
Location: Bulgaria

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Might need to be a union, although conditional clauses may be supported in order by..
Behzad
Forum Commoner
Posts: 28
Joined: Mon Jul 09, 2007 3:24 pm
Location: Tehran, Iran
Contact:

Post 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)?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

How about something as following:

Code: Select all

SELECT *, @OrderId := IF(ID < 10, 10 - ID, ID)
FROM foo
ORDER BY @OrderId ASC;
Behzad
Forum Commoner
Posts: 28
Joined: Mon Jul 09, 2007 3:24 pm
Location: Tehran, Iran
Contact:

Post 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?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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
Behzad
Forum Commoner
Posts: 28
Joined: Mon Jul 09, 2007 3:24 pm
Location: Tehran, Iran
Contact:

Post by Behzad »

ٌٌٌٌٌٌٌWWWWWWWWWWWWOOWW!
VERY INTERESTING!
Behzad
Forum Commoner
Posts: 28
Joined: Mon Jul 09, 2007 3:24 pm
Location: Tehran, Iran
Contact:

Post 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;
Last edited by Behzad on Fri Jul 27, 2007 9:49 am, edited 1 time in total.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
Behzad
Forum Commoner
Posts: 28
Joined: Mon Jul 09, 2007 3:24 pm
Location: Tehran, Iran
Contact:

Post by Behzad »

Oops. I'm sorry.
I edited the my previous post.
But the question still remains.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

onion2k's code should work fine. It works using the primary key.
Post Reply