On Mon, 9 Sep 2002 18:08:21 +1000
"jack" <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
