Re: the best way to get the topest 3 record in every group - Mailing list pgsql-sql

On Mon, 9 Sep 2002 18:08:21 +1000
"jack" <datactrl@tpg.com.au> wrote:

> Dima
> My question is that I want to produce ALL the lastest 3 records for EACH
> itemNo and supplier.
> 
> Jack


I often use the next query for ranking, which is equivalent to Oracle's
PARTITION BY().  It isn't influenced by what kind of data your table has;
it only depends on the result of sorting


CREATE TEMP SEQUENCE seq_purchase;

SELECT t5.item_no, t5.supplier, t5.purchase_date,               t5.price, t5.qty, t5.i - t3.n + 1 AS rank
FROM (SELECT t2.item_no, t2.supplier, min(t2.i) AS n        FROM (SELECT t1.*, nextval('seq_purchase') - 1 AS i
      FROM (SELECT (SELECT setval('seq_purchase',1)), *                     FROM purchase                    ORDER BY
item_no,supplier, purchase_date desc                  ) AS t1              LIMIT ALL            ) AS t2     GROUP BY
t2.item_no,t2.supplier    ) AS t3,    (SELECT t4.*, nextval('seq_purchase') - 1 AS i       FROM (SELECT (SELECT
setval('seq_purchase',1)),*               FROM purchase            ORDER BY item_no, supplier, purchase_date DESC
    ) AS t4       LIMIT ALL    ) AS t5
 
WHERE t3.item_no = t5.item_no AND t3.supplier = t5.supplier AND t5.i - t3.n + 1 <= 3
;


Regards,
Masaru Sugawara