pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect last there dates
9+ messages / 4 participants
[nested] [flat]
* Select last there dates
@ 2007-06-21 08:18 Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-21 09:18 ` Re: Select last there dates Richard Huxton <dev@archonet.com>
2007-06-21 09:50 ` Re: [SQL] Select last there dates A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 13:47 ` Re: Select last there dates David Gardner <david@gardnerit.net>
0 siblings, 3 replies; 9+ messages in thread
From: Loredana Curugiu @ 2007-06-21 08:18 UTC (permalink / raw)
To: pgsql-novice@postgresql.org; 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Select last there dates
2007-06-21 08:18 Select last there dates Loredana Curugiu <loredana.curugiu@gmail.com>
@ 2007-06-21 09:18 ` Richard Huxton <dev@archonet.com>
2 siblings, 0 replies; 9+ messages in thread
From: Richard Huxton @ 2007-06-21 09:18 UTC (permalink / raw)
To: Loredana Curugiu <loredana.curugiu@gmail.com>; +Cc: pgsql-novice@postgresql.org; pgsql-sql
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;
END IF;
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Select last there dates
2007-06-21 08:18 Select last there dates Loredana Curugiu <loredana.curugiu@gmail.com>
@ 2007-06-21 09:50 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 13:00 ` Re: [SQL] Select last there dates Loredana Curugiu <loredana.curugiu@gmail.com>
2 siblings, 1 reply; 9+ messages in thread
From: A. Kretschmer @ 2007-06-21 09:50 UTC (permalink / raw)
To: pgsql-sql; pgsql-novice@postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Select last there dates
2007-06-21 08:18 Select last there dates Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-21 09:50 ` Re: [SQL] Select last there dates A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2007-06-21 13:00 ` Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-21 13:18 ` Re: Select last there dates Richard Huxton <dev@archonet.com>
2007-06-21 13:24 ` Re: [SQL] Select last there dates A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 2 replies; 9+ messages in thread
From: Loredana Curugiu @ 2007-06-21 13:00 UTC (permalink / raw)
To: dev@archonet.com; andreas.kretschmer@schollglas.com; pgsql-novice@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Select last there dates
2007-06-21 08:18 Select last there dates Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-21 09:50 ` Re: [SQL] Select last there dates A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 13:00 ` Re: [SQL] Select last there dates Loredana Curugiu <loredana.curugiu@gmail.com>
@ 2007-06-21 13:18 ` Richard Huxton <dev@archonet.com>
2007-06-21 13:33 ` Re: [SQL] Select last there dates Loredana Curugiu <loredana.curugiu@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Richard Huxton @ 2007-06-21 13:18 UTC (permalink / raw)
To: Loredana Curugiu <loredana.curugiu@gmail.com>; +Cc: andreas.kretschmer@schollglas.com; pgsql-novice@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Select last there dates
2007-06-21 08:18 Select last there dates Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-21 09:50 ` Re: [SQL] Select last there dates A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 13:00 ` Re: [SQL] Select last there dates Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-21 13:18 ` Re: Select last there dates Richard Huxton <dev@archonet.com>
@ 2007-06-21 13:33 ` Loredana Curugiu <loredana.curugiu@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Loredana Curugiu @ 2007-06-21 13:33 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; pgsql-novice@postgresql.org; pgsql-sql
>
>
> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Select last there dates
2007-06-21 08:18 Select last there dates Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-21 09:50 ` Re: [SQL] Select last there dates A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 13:00 ` Re: [SQL] Select last there dates Loredana Curugiu <loredana.curugiu@gmail.com>
@ 2007-06-21 13:24 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 13:35 ` Re: [SQL] Select last there dates Loredana Curugiu <loredana.curugiu@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: A. Kretschmer @ 2007-06-21 13:24 UTC (permalink / raw)
To: pgsql-novice@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Select last there dates
2007-06-21 08:18 Select last there dates Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-21 09:50 ` Re: [SQL] Select last there dates A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 13:00 ` Re: [SQL] Select last there dates Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-21 13:24 ` Re: [SQL] Select last there dates A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2007-06-21 13:35 ` Loredana Curugiu <loredana.curugiu@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Loredana Curugiu @ 2007-06-21 13:35 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; pgsql-novice@postgresql.org; pgsql-sql
Thank you for documentation.
Best regards,
Loredana
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Select last there dates
2007-06-21 08:18 Select last there dates Loredana Curugiu <loredana.curugiu@gmail.com>
@ 2007-06-21 13:47 ` David Gardner <david@gardnerit.net>
2 siblings, 0 replies; 9+ messages in thread
From: David Gardner @ 2007-06-21 13:47 UTC (permalink / raw)
To: Loredana Curugiu <loredana.curugiu@gmail.com>; pgsql-novice@postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-06-21 13:47 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-21 08:18 Select last there dates Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-21 09:18 ` Richard Huxton <dev@archonet.com>
2007-06-21 09:50 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 13:00 ` Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-21 13:18 ` Richard Huxton <dev@archonet.com>
2007-06-21 13:33 ` Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-21 13:24 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 13:35 ` Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-21 13:47 ` David Gardner <david@gardnerit.net>
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