List: General Discussion « Previous MessageNext Message » From: Martin Ramsch Date: July 27 1999 10:35am Subject: Re: Really *REALLY* weird ORDER BY View as plain text
```Replying to a rather old message from Tue, 1999-07-08 23:14:59 -0600.
Bob Worthy wrote:

> Para-dox wrote:
[...]
> > [...] have to be sorted in a special "bell-shaped way."
[...]
> > The most expensive ones are in the middle, while the least
> > expensive are on the outside, this can be accomplished by grabbing
> > them lowest to highest and alternating inserting them from the top
> > and bottom. How can I do this in an SQL ORDER BY?

Another funny solution (though not very efficient, I guess) is to
calculate a row index and then use this one.

I can get a row index by doing a full self join over the table and
counting the successful comparisons:

SELECT SUM(t1.n>=t2.n) AS i, t1.n
FROM testtable AS t1, testtable AS t2
GROUP BY t1.n ORDER BY i;

Or for reverse numbering:

SELECT SUM(t1.n<t2.n) AS i, t1.n
FROM testtable AS t1, testtable AS t2
GROUP BY t1.n ORDER BY i;

To yield "bell sorting" we use reverse numbering with alternating
sign:

SELECT IF( SUM(t1.n<t2.n) % 2, SUM(i1.n<i2.n), -SUM(i1.n<i2.n) ) AS i
,t1.n
FROM testtable AS t1, testtable AS t2
GROUP BY t1.n ORDER BY i;

Side note:
I would really appreciate if MySQL had a pseudo column for the row
index.  Sometimes that could be quite handy ...

Regards,
Martin
--
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
```