Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2C83D9FB41C; Thu, 21 Jun 2007 06:50:42 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 54572-05; Thu, 21 Jun 2007 06:50:29 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.183]) by postgresql.org (Postfix) with ESMTP id E70AC9FB48D; Thu, 21 Jun 2007 06:50:28 -0300 (ADT) Received: from [194.25.154.138] (helo=webserv.wug.de) by mrelayeu.kundenserver.de (node=mrelayeu2) with ESMTP (Nemesis), id 0MKwtQ-1I1JJ422BW-0003De; Thu, 21 Jun 2007 11:50:27 +0200 Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1) id 1I1JJ3-0002co-00; Thu, 21 Jun 2007 11:50:25 +0200 Date: Thu, 21 Jun 2007 11:50:25 +0200 From: "A. Kretschmer" To: pgsql-sql@postgresql.org, pgsql-novice@postgresql.org Subject: Re: [SQL] Select last there dates Message-ID: <20070621095025.GG1405@a-kretschmer.de> References: <1c23c8e70706210118h6b9490e3s11e2a6ba15cdff6c@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1c23c8e70706210118h6b9490e3s11e2a6ba15cdff6c@mail.gmail.com> X-OS: Debian/GNU Linux - weil ich es mir Wert bin! X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the answer is "no"! User-Agent: Mutt/1.5.9i X-Provags-ID: V01U2FsdGVkX1/+dET9AvYqfFS3yTRmuqYSn03/orqQ44vXYWG IOdCxAxtaQsSf3IdGwnuhj6up6ZH2mZPfNEB2cLJ1yutsITdHN vrmd/mEc0OxApDWba05dKEHX9ILKHME X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/146 X-Sequence-Number: 19044 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