Re: [SQL] Select last there dates - Mailing list pgsql-novice

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