Select last there dates

Поиск
Список
Период
Сортировка
От Loredana Curugiu
Тема Select last there dates
Дата
Msg-id 1c23c8e70706210118h6b9490e3s11e2a6ba15cdff6c@mail.gmail.com
обсуждение исходный текст
Ответы Re: Select last there dates  (Richard Huxton <dev@archonet.com>)
Re: Select last there dates  ("A. Kretschmer" <andreas.kretschmer@schollglas.com>)
Список pgsql-sql
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

В списке pgsql-sql по дате отправления:

Предыдущее
От: "A. Kretschmer"
Дата:
Сообщение: Re: Technique for turning time ranges into a graph
Следующее
От: Richard Huxton
Дата:
Сообщение: Re: Select last there dates