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:

Post by Behzad »

IT WORKS even without a primary key. onion2k is a SQL GURU INDEED.
Behzad
Forum Commoner
Posts: 28
Joined: Mon Jul 09, 2007 3:24 pm
Location: Tehran, Iran
Contact:

Post by Behzad »

The final answer based on onion2k's approach:

Code: Select all

SELECT
	d.id,
	@counter:=@counter+1,
	IF (@counter<=10, d.id, 0) AS descOrder
FROM
	(SELECT * FROM numbers ORDER by id ASC) d,
	(SELECT @counter:=0) c
ORDER BY
	descOrder DESC,
	d.id ASC
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post by nathanr »

I like onions approach very much, here's a procedureless version with percentages that seems to work perfectly on mysql 5

Code: Select all

SELECT * FROM (select * from numbers where id <(select round(count(*)/100*50) as d from numbers order by id) order by id DESC) as a
UNION
SELECT * FROM (select * from numbers where id >=(select round(count(*)/100*50) as d from numbers order by id) order by id ASC) as b;
where 50 is the percentage..

:)
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

While I'm sure that works I can't help thinking that 6 selects in a single query is not going to scale very well. Do you need the outermost SELECT statements in each half? I can't see what they achieve. Plus, "select round(count(*)/100*50)" could be moved into a variable. That would mean there's 2 queries rather than one, but it'd be quicker nonetheless.
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post by nathanr »

I agreee completely, I wouldn't use this myself in any serious application, it can also be optimised a great deal further; the outer selects are a quick and dirty fix to the order by problem, the round(blah) was just a simple way of keeping it all inline.

I must stress I was simply throwing this into the equation as an alternative method, the sql works, even though it only took about 20 seconds to write. Personally I have some queries with regards the functional use of running something like this, but hey there must be a need. Personally I think that just because something *can* be done in mysql, doesn't mean to say it *should* be done.

There are many areas that have not been covered in the discussions so far, for example what if the numbers ran from 1-22 then 32-50, should we split at 50% of the numbers (thus 20) or at half of the largest int (thus 25)?

To be honest, I think that some more details from the original poster in regards to what exactly he is trying to achive would aid discussion(s), surely there has to be a functional need for this outside of the scope of a single numebrs table, otherwise we'd just php a couple of for loops or similar.
Post Reply