Re: [SQL] Select last there dates

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