Re: Select last there dates

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