Skip site navigation (1) Skip section navigation (2)

Re: [SQL] Select last there dates

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-sql(at)postgresql(dot)org, pgsql-novice(at)postgresql(dot)org
Subject: Re: [SQL] Select last there dates
Date: 2007-06-21 09:50:25
Message-ID: 20070621095025.GG1405@a-kretschmer.de (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-sql
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

In response to

Responses

pgsql-novice by date

Next:From: Marcus EngeneDate: 2007-06-21 10:49:15
Subject: stem override, tsearch2
Previous:From: Richard HuxtonDate: 2007-06-21 09:18:29
Subject: Re: Select last there dates

pgsql-sql by date

Next:From: A. KretschmerDate: 2007-06-21 10:04:25
Subject: Re: Results per letter query
Previous:From: Richard HuxtonDate: 2007-06-21 09:18:29
Subject: Re: Select last there dates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group