Sorry, I'm using 8.1, not 8.4. On Tue, May 17, 2011 at 10:06 AM, Gerhard Hintermayer < gerhard.hinterma...@gmail.com> wrote:
> Hi, > is there a way to sort the joined tuples in the way they are in a the > joined array ? BTW I'm using 8.4 (_I_ know I should upgrade, but management > thinks we shouldn't :-( ) > I'd like to join 2 tables based on a column, where the column is an array > in one table, but I still need to keep the order of tuples as they were > originally in the array. > > What I now get is e.g. if the array contains A,B,C , I get rows B, C and A, > but I'd like to get one row containing A, the B, then C > > My query is: > select * from produkt inner join (select a_nr,komp as p_code from r_mi_sfm > where a_nr=20110) as auftrag on (produkt.p_code = any(auftrag.p_code)); > > and explain says: > Nested Loop (cost=201.83..2656.51 rows=26992 width=98) > Join Filter: ("inner".p_code = ANY ("outer".komp)) > -> Index Scan using idx_r_mi_sfm_a_nr on r_mi_sfm (cost=0.00..25.39 > rows=7 width=58) > Index Cond: (a_nr = 20110) > -> Materialize (cost=201.83..278.95 rows=7712 width=40) > -> Seq Scan on produkt (cost=0.00..194.12 rows=7712 width=40) > > thanks for any input > Gerhard >