Page 2 of 2

Posted: Fri Jul 27, 2007 10:12 am
by Behzad
IT WORKS even without a primary key. onion2k is a SQL GURU INDEED.

Posted: Fri Jul 27, 2007 10:32 am
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

Posted: Sat Jul 28, 2007 6:32 pm
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..

:)

Posted: Sun Jul 29, 2007 5:18 am
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.

Posted: Sun Jul 29, 2007 7:39 am
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.