pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: A. Kretschmer <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
To: pgsql-novice@postgresql.org
Subject: Re: [SQL] Select last there dates
Date: Thu, 21 Jun 2007 11:50:25 +0200
Message-ID: <20070621095025.GG1405@a-kretschmer.de> (raw )
In-Reply-To: <1c23c8e70706210118h6b9490e3s11e2a6ba15cdff6c@mail.gmail.com >
References: <1c23c8e70706210118h6b9490e3s11e2a6ba15cdff6c@mail.gmail.com >
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
view thread (9+ messages) latest in thread
Message-ID: <20070621095025.GG1405@a-kretschmer.de>
Permalink: ../20070621095025.GG1405@a-kretschmer.de/
Also on: postgresql.org/message-id/20070621095025.GG1405@a-kretschmer.de
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas.kretschmer@schollglas.com, pgsql-novice@postgresql.org
Subject: Re: [SQL] Select last there dates
In-Reply-To: <20070621095025.GG1405@a-kretschmer.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox