Re: Problem with result ordering

am Thu, dem 25.01.2007, um 16:45:23 +0100 mailte Thorsten Körner folgendes:
> Hi,
>
> when I fire the following query:
> select m_id, m_u_id, m_title, m_rating from tablename where m_id in (26250,
> 11042, 16279, 42197, 672089);
>
> I will get the same results in the same order, as in in the next query:
> select m_id, m_u_id, m_title, m_rating from tablename where m_id in
> (11042,42197,672089,26250,16279);
>
> I wonder, how it is possible, to retrieve the results in the same order, as
> queried in the list. The listed IDs are from an application outside the
> database.

a little trick;

store your order-definition and the where-condition in a separate table.

Our table:
test=*# select * from foo;
id | val
----+-------
1 | text1
2 | text2
3 | text3
4 | text4
5 | text5
(5 rows)

Our sort-order:
-- col a: the sort-order
-- col b: the where-condition
test=*# select * from o;
a | b
---+---
1 | 3
2 | 5
3 | 1
(3 rows)

test=*# select foo.id, foo.val from foo join o on foo.id=o.b order by o.a;
id | val
----+-------
3 | text3
5 | text5
1 | text1
(3 rows)

You can try it without this table, only with generate_series or so.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net