Thread: Select last there dates

Select last there dates

From

"Loredana Curugiu"

Date:

21 June 2007, 08:18:25

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

Re: [SQL] Select last there dates

From

"A. Kretschmer"

Date:

21 June 2007, 09:50:42

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

Re: [SQL] Select last there dates

From

"Loredana Curugiu"

Date:

21 June 2007, 13:00:37

Richard, Andreas,

thank you very much for your solutions. I took a look on
both solutions, but I choosed Andreas's solution because
is shorter :)

So Andreas, would you please give some more explanations
on your solution? I didn't work with functions and aggregate till
now.

I don't understand how this comma_aggregate works. I can see that this
function it is defined with two arguments, but the aggredate it is called
with a single argument.

And what is LANGUAGE sql IMMUTABLE STRICT ?

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='' );

Best regards,
Loredana

Re: [SQL] Select last there dates

From

"A. Kretschmer"

Date:

21 June 2007, 13:24:54

am  Thu, dem 21.06.2007, um 16:00:05 +0300 mailte Loredana Curugiu folgendes:
> So Andreas, would you please give some more explanations
> on your solution? I didn't work with functions and aggregate till
> now.

I will try it, but i'm not a nativ english speaker and thats why i have
some problems...

>
> I don't understand how this comma_aggregate works. I can see that this
> function it is defined with two arguments, but the aggredate it is called
> with a single argument.

An aggregate needs among others a so called 'SFUNC' with 2 parameters:
internal-state and next data item. It returns the next-internal-state.

This is explained here:
http://www.postgresql.org/docs/8.1/interactive/sql-createaggregate.html

>
> And what is LANGUAGE sql IMMUTABLE STRICT ?

An IMMUTABLE function can't modify the database
http://www.postgresql.org/docs/current/static/xfunc-volatility.html


STRICT:
http://www.postgresql.org/docs/8.2/static/xfunc-c.html

,----[  ]
| Notice that we have specified the functions as "strict", meaning that
| the system should automatically assume a null result if any input value
| is null.
`----



Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: [SQL] Select last there dates

From

"Loredana Curugiu"

Date:

21 June 2007, 13:33:14

I think there might be a small typo though. The left-join is to:
(select uid, phone_numer, datum from t2 order by 2 )
Probably want to order by "datum DESC" too, to ensure you get the latest
dates for each telnum.

yes, I also observed this, so I have add to my query.

Thank you for your explanations, Richard.

Best regards,
Loredana

Re: [SQL] Select last there dates

From

"Loredana Curugiu"

Date:

21 June 2007, 13:35:44

Thank you for documentation.

Best regards,
Loredana

Re: Select last there dates

From

David Gardner

Date:

21 June 2007, 13:48:41

I'm not sure about getting all the dates into a single comma separated
field, if table two had a unique id field, say table2.id then  this
should retrieve the last three dates per phone number:

SELECT table1.uid, table1.phone_number, table2.id, table2.date
FROM table1 JOIN table2 ON table1.uid=table2.uid
AND table1.phone_number=table2.phone_number
WHERE table1.uid = 8
AND (table2.id IN
(SELECT id FROM table2
WHERE table2.phone_number = table1.phone_number
ORDER BY table2.date DESC LIMIT 3 ));


--
David Gardner