Message-ID: <467A4265.20406@archonet.com>
Date: Thu, 21 Jun 2007 10:18:29 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Loredana Curugiu <loredana.curugiu@gmail.com>
Cc: pgsql-novice@postgresql.org, pgsql-sql@postgresql.org
Subject: Re: Select last there dates
References: <1c23c8e70706210118h6b9490e3s11e2a6ba15cdff6c@mail.gmail.com>
In-Reply-To: <1c23c8e70706210118h6b9490e3s11e2a6ba15cdff6c@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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