Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id D31379FB6F7 for ; Thu, 21 Jun 2007 05:18:21 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 55280-06 for ; Thu, 21 Jun 2007 05:18:12 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.241]) by postgresql.org (Postfix) with ESMTP id A7D0B9FB5EC for ; Thu, 21 Jun 2007 05:18:14 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c36so91477ana for ; Thu, 21 Jun 2007 01:18:13 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; b=OYqioi67fQWl8KeSDO1CtEvg3Hj1ZHMrqmqD5NL+B5uU8UofYsLSnFikyWACKkMQmak/iS8mSBuvT8e74BWcHQe7K7J/dZKVY8ng0lWMVk7le5LWXtIJ5jaql57dXynrzGHkQdkol2LO5PJf1AOE9SieqVDhSjcJ5mNa9U+3odE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=F9t8WvRIf6I1TLFOS3/mkr5toqY5wA442a7+VfmVujBwng0Khdm1gcsPd+Wx+L/NAWn7BfB96/lc2elckNC6wV1W3wkKBm6gjoyrwjIJNVD7ZywUxFFs6kPHfoulLea6mw2HW4wAq+RBMrpKTElcctk4YvG6HuPyWtzH1KE9tPc= Received: by 10.100.128.8 with SMTP id a8mr952432and.1182413893754; Thu, 21 Jun 2007 01:18:13 -0700 (PDT) Received: by 10.100.144.11 with HTTP; Thu, 21 Jun 2007 01:18:13 -0700 (PDT) Message-ID: <1c23c8e70706210118h6b9490e3s11e2a6ba15cdff6c@mail.gmail.com> Date: Thu, 21 Jun 2007 11:18:13 +0300 From: "Loredana Curugiu" To: pgsql-novice@postgresql.org, pgsql-sql@postgresql.org Subject: Select last there dates MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_133117_30387346.1182413893647" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/145 X-Sequence-Number: 19043 ------=_Part_133117_30387346.1182413893647 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ------=_Part_133117_30387346.1182413893647 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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

------=_Part_133117_30387346.1182413893647--