Date: Tue, 10 Sep 2002 01:23:29 +0900
From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: "jack" <datactrl@tpg.com.au>
Subject: Re: the best way to get the topest 3 record in every group
Cc: pgsql-sql@postgresql.org
In-Reply-To: <000701c257d8$1163c9a0$1400a8c0@jac>
References: <3D7C4108.70407@mail.ru> <000701c257d8$1163c9a0$1400a8c0@jac>
Errors-To: rk73@sea.plala.or.jp
Message-Id: <20020910012302.828A.RK73@sea.plala.or.jp>
MIME-Version: 1.0
Content-Type: text/plain; charset="US-ASCII"
Content-Transfer-Encoding: 7bit

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