From: Richard Huxton <dev@archonet.com>
To: Loredana Curugiu <loredana.curugiu@gmail.com>
Cc: pgsql-novice@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Select last there dates
Date: Thu, 21 Jun 2007 10:18:29 +0100
Message-ID: <467A4265.20406@archonet.com> (raw)
In-Reply-To: <1c23c8e70706210118h6b9490e3s11e2a6ba15cdff6c@mail.gmail.com>
References: <1c23c8e70706210118h6b9490e3s11e2a6ba15cdff6c@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, loredana.curugiu@gmail.com, pgsql-novice@postgresql.org
Subject: Re: Select last there dates
In-Reply-To: <467A4265.20406@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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