selecting data from subquery in same order

On Sat, Aug 16, 2008 at 6:11 PM, mark <markkicks@gmail.com> wrote:

hi
if i execute this statement:

select * from users where id in (2341548, 2325251, 2333130, 2015421,
2073536, 2252374, 2273219, 2350850, 2367318, 2032977, 2032849, )

the order of rows obtained is random.

is there anyway i can get the rows in the same order as the ids in
subquery? or is there a different statement i can use?
thanks!

Technically, that's just a list, not a subquery, but that's not
important right now.

You can use a case statement.

select field1, field2, idfield from users where id in (1,4,3) order by
case
when idfield=1 then 1
when idfield=3 then 2
when idfield=4 then 3
end

On Sat, Aug 16, 2008 at 8:07 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

On Sat, Aug 16, 2008 at 6:11 PM, mark <markkicks@gmail.com> wrote:

hi
if i execute this statement:

select * from users where id in (2341548, 2325251, 2333130, 2015421,
2073536, 2252374, 2273219, 2350850, 2367318, 2032977, 2032849, )

the order of rows obtained is random.

is there anyway i can get the rows in the same order as the ids in
subquery? or is there a different statement i can use?
thanks!

Technically, that's just a list, not a subquery, but that's not
important right now.

You can use a case statement.

select field1, field2, idfield from users where id in (1,4,3) order by
case
when idfield=1 then 1
when idfield=3 then 2
when idfield=4 then 3
end

oops, that should be

when idfield=1 then 1
when idfield=4 then 2
when idfield=3 then 3

You may use something like this in a stored function:

DECLARE
a INTEGER[];
BEGIN
a := '{2341548, 2325251, 2333130, 2015421,2073536, 2252374, 2273219,
2350850, 2367318, 2032977, 2032849}';
select * from users where id = any(a) order by idx(a, id);
END;

Or in the plain SQL:

select * from users where id = any(a) order by idx('{2341548, 2325251,
2333130, 2015421,2073536, 2252374, 2273219, 2350850, 2367318, 2032977,
2032849}', id);

Note that it is pretty fast only if the array contains not too much elements
(e.g. 20). Do not use for large arrays!

On Sun, Aug 17, 2008 at 4:11 AM, mark <markkicks@gmail.com> wrote: