From: Loredana Curugiu <loredana.curugiu@gmail.com>
To: pgsql-novice@postgresql.org
To: pgsql-sql@postgresql.org
Subject: Select last there dates
Date: Thu, 21 Jun 2007 11:18:13 +0300
Message-ID: <1c23c8e70706210118h6b9490e3s11e2a6ba15cdff6c@mail.gmail.com> (raw)
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 |
I created the query
SELECT table1.phone_number,
TO_CHAR( table2.date, 'YYYY-MM-DD' ) AS date
FROM ( SELECT * FROM table1 WHERE uid=8 ) table1
LEFT JOIN table2
ON table1.uid=8
AND table1.uid=table2.uid
AND table1.phone_number=table2.phone_number
which returns.
phone_number | date
--------------+------------
+40741775621 | 2007-06-18
+40741775621 | 2007-06-19
+40741775621 | 2007-06-20
+40741775621 | 2007-03-21
+40741775621 | 2007-04-21
+40741775621 | 2007-05-21
+40741775621 | 2007-06-21
+40741775622 | 2007-06-15
+40741775622 | 2007-06-16
+40741775623 |
I don't know how to use this result for obtaining the result I need.
I was thinking to get the dates for the phone_number into a array
and then to use array_to string function, but I don't know how to do it.
Any help, please?
Loredana
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: loredana.curugiu@gmail.com, pgsql-novice@postgresql.org
Subject: Re: Select last there dates
In-Reply-To: <1c23c8e70706210118h6b9490e3s11e2a6ba15cdff6c@mail.gmail.com>
* 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