Strange ORDER BY! A Very Interesting Question
Moderator: General Moderators
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 ASCI like onions approach very much, here's a procedureless version with percentages that seems to work perfectly on mysql 5
where 50 is the percentage..

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;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.
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.
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.