Re: [GENERAL] selecting data from subquery in same order
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 <[EMAIL PROTECTED]> 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!
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Re: [GENERAL] selecting data from subquery in same order
On Sat, Aug 16, 2008 at 8:07 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Sat, Aug 16, 2008 at 6:11 PM, mark <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] selecting data from subquery in same order
On Sat, Aug 16, 2008 at 6:11 PM, mark <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] selecting data from subquery in same order
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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
