Thread: Select last there dates
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
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
Loredana Curugiu wrote:
> 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 |
You either need a subquery with a LIMIT, or you could write a custom
aggregate (see below):
BEGIN;
CREATE TABLE telnum_date_test ( telnum text, teldate date
);
INSERT INTO telnum_date_test SELECT '0123 456 789','2007-01-10'::date -
generate_series(0,9);
INSERT INTO telnum_date_test SELECT '0234 567 890','2007-02-10'::date -
generate_series(0,9);
SELECT * FROM telnum_date_test ORDER BY telnum,teldate;
CREATE FUNCTION date_top3_acc(topvals date[], newval date) RETURNS
date[] AS $$
DECLARE i int4; j int4; n int4; outvals date[];
BEGIN -- array_upper returns null for an empty array and 1 for a 1
element array n := COALESCE( array_upper(topvals, 1), 0 ); j := 1;
-- I suppose you could think of this as an insert-sort with an
upper bound FOR i IN 1..n LOOP IF newval > topvals[i] AND j <= 3 THEN outvals[j] := newval;
j := j + 1; END IF; IF j <= 3 THEN outvals[j] := topvals[i]; j := j + 1;
ENDIF; END LOOP;
IF j <= 3 THEN outvals[j] := newval; END IF;
RETURN outvals;
END;
$$ LANGUAGE plpgsql;
CREATE AGGREGATE top3(date) ( sfunc = date_top3_acc, stype = date[], initcond = '{}'
);
SELECT telnum, top3(teldate) FROM telnum_date_test GROUP BY telnum ORDER
BY telnum;
COMMIT;
-- Richard Huxton Archonet Ltd
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
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 ?
Best regards,
Loredana
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
Loredana Curugiu wrote: > 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 :) Not to mention clever, exploiting the fact that we know the length of a text-representation of three comma-separated dates. 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. > 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. The comma_aggregate function takes two parameters (RUNNING-TOTAL, NEW-VALUE). The result forms the RUNNING-TOTAL for the next call. This RUNNING-TOTAL has a type set by "stype" below and with an initial value set by "initcond". > And what is LANGUAGE sql IMMUTABLE STRICT ? The function is pure SQL (mine was procedural plpgsql, other languages are available). It's IMMUTABLE because the output depends only on the inputs, not the contents of the database and begin STRICT if input values are NULL then the output is automatically NULL. > 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='' ); -- Richard Huxton Archonet Ltd
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
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
Thank you for documentation.
Best regards,
Loredana
Best regards,
Loredana