Re: Select last there dates - Mailing list 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;
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