am Thu, dem 21.06.2007, um 11:18:13 +0300 mailte Loredana Curugiu folgendes: > Hello again, > > I have the following two tables: > > Table 1: > uid | phone_number | > -----+------------------------------- > 8 | +40741775621 | > 8 | +40741775622 | > 8 | +40741775623 | > 9 | +40741775621 | > 9 | +40741775622 | > 9 | +40741775623 | > 10 | +40741775621 | > 10 | +40741775622 | > 10 | +40741775623 | > 7 | +40741775621 | > 7 | +40741775622 | > 7 | +40741775623 | > 11 | +40741775621 | > 11 | +40741775622 | > 11 | +40741775623 | > > Table2: > > uid | phone_number | date > ---------------+-----------------------+------------------------------- > 8 | +40741775621 | 2007-06-21 10:40:00+00 > 8 | +40741775621 | 2007-05-21 10:40:00+00 > 8 | +40741775621 | 2007-04-21 10:40:00+00 > 8 | +40741775621 | 2007-03-21 10:40:00+00 > 8 | +40741775621 | 2007-06-20 10:40:00+00 > 8 | +40741775621 | 2007-06-19 10:40:00+00 > 8 | +40741775621 | 2007-06-18 10:40:00+00 > 8 | +40741775622 | 2007-06-16 10:40:00+00 > 8 | +40741775622 | 2007-06-15 10:40:00+00 > 7 | +40741775622 | 2007-06-21 05:54:13.646457+00 > 7 | +40741775621 | 2007-06-21 05:54:21.134469+00 > > > For each uid column from table1 I have different values phone_number > column. > > For each uid and phone_number columns from table2 I have different > values for date column. > > My task is to create a query which for a given uid returns all values > for phone_number column from table1 and last three values of date > column from table2. > > For example, if uid=8 the query should return: > > phone_number | date > -----------------------+------------ > +40741775621 | 2007-06-21, 2007-06-20, 2007-06-19 > +40741775622 | 2007-06-16, 2007-06-15 > +40741775623 |
lets try: first, i need a comma-aggregat: CREATE FUNCTION comma_aggregate(text,text) RETURNS text AS ' SELECT CASE WHEN $1 <> '''' THEN $1 || '', '' || $2 ELSE $2 END; ' LANGUAGE sql IMMUTABLE STRICT; CREATE AGGREGATE comma (basetype=text, sfunc=comma_aggregate, stype=text, initcond='' ); Now your tables, with a typo in the phone_number - column, sorry ;-) test=*# select * from t1; uid | phone_numer -----+------------- 8 | 40741775621 8 | 40741775622 8 | 40741775623 9 | 40741775621 9 | 40741775622 9 | 40741775623 10 | 40741775621 10 | 40741775622 10 | 40741775623 (9 rows) test=*# select * from t2; uid | phone_numer | datum -----+-------------+------------ 8 | 40741775621 | 2007-06-21 8 | 40741775621 | 2007-05-21 8 | 40741775621 | 2007-04-21 8 | 40741775621 | 2007-03-21 8 | 40741775621 | 2007-06-20 8 | 40741775621 | 2007-06-19 8 | 40741775621 | 2007-06-18 8 | 40741775622 | 2007-06-16 8 | 40741775622 | 2007-06-15 7 | 40741775622 | 2007-06-21 7 | 40741775621 | 2007-06-21 (11 rows) And now: test=*# select t1.phone_numer, substring(comma(t2.datum) from 1 for 34) from t1 left outer join (select uid, phone_numer, datum from t2 order by 2 ) t2 on (t1.uid,t1.phone_numer)=(t2.uid,t2.phone_numer) where t1.uid=8 group by t1.phone_numer; phone_numer | substring -------------+------------------------------------ 40741775621 | 2007-06-21, 2007-05-21, 2007-04-21 40741775622 | 2007-06-16, 2007-06-15 40741775623 | (3 rows) Perhaps there are better solutions possible... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
