PHP SQL - order 1 3 2

Author

order 1 3 2

Florian

2004-11-19, 3:57 pm

Hello
I can't find how to order my resultset that way :
ID
---
1
3
2

any idea ?

Thanx !

Flo

Hilarion

2004-11-19, 3:57 pm

What database you use?

In Oracle 8i (and probably newer versions too) you could do:

SELECT id
FROM my_table
ORDER BY DECODE( id, 1, 1, 3, 2, 2, 3, id )

In MS SQL Server 2000 you could do:

SELECT id
FROM my_table
ORDER BY CASE id WHEN 1 THEN 1 WHEN 3 THEN 2 WHEN 2 THEN 3 ELSE id END

Hilarion

Vincent Lascaux

2004-11-22, 8:56 pm

> ID
> ---
> 1
> 3
> 2

ORDER BY ID*(9-2*ID)

--
Vincent
More unreadable code ;)

Hilarion

2004-11-23, 8:56 am

>> ID
>
> ORDER BY ID*(9-2*ID)

Nice. How did you get to this idea? I suppose there's
some math behind it (computing formula based on three
coordinates, but why those three points?).

Hilarion

Vincent Lascaux

2004-11-23, 3:56 pm

>> ORDER BY ID*(9-2*ID)
>
> Nice. How did you get to this idea? I suppose there's
> some math behind it (computing formula based on three
> coordinates, but why those three points?).

f(x) = -(x-a)² takes it maximum in a and is symetrical / a.
You want f(1) < f(3) < f(2). a must be closer to 2 so that f(2) is the
highest value and one the side of 3 so that f(3) > f(1).
Any a between 2 and 2.5 exclusive is ok (smaller than 2 and f(1) > f(3) and
larger than 2.5 and f(3) > f(2)). I chose 2.25

-(x-2.25)² = -x² + 4.5x - 2.25². You can get rid of the constant since the
only interesting thing is the ordering (not the value of f) and I multiplied
everything by 2 to have integer values. -2x²+9x = x(9-2x)

--
Vincent

Sponsored Links