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
Thread
Really *REALLY* weird ORDER BYPara-dox9 Jul
  • Re: Really *REALLY* weird ORDER BYSasha Pachev9 Jul
    • Re: Really *REALLY* weird ORDER BYBob Worthy9 Jul
      • Re: Really *REALLY* weird ORDER BYMartin Ramsch27 Jul