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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
