Thread: the best way to get the topest 3 record in every group
Hi, There is a table like : << itemNo supplier purchaseDate Price Qty << Please provide an idea if I want to get the latest 3 puchase records for each item and supplier. Thank you in advance. jack
> There is a table like : > << > itemNo > supplier > purchaseDate > Price > Qty > << > Please provide an idea if I want to get the latest 3 puchase records for > each item and supplier. Thank you in advance. select * from table_name where supplier=value order by purchaseDate desc limit 3 ???
Dima My question is that I want to produce ALL the lastest 3 records for EACH itemNo and supplier. Jack ----- Original Message ----- From: "dima" <_pppp@mail.ru> To: "jack" <datactrl@tpg.com.au> Cc: <pgsql-sql@postgresql.org> Sent: Monday, September 09, 2002 4:34 PM Subject: Re: [SQL] the best way to get the topest 3 record in every group > > There is a table like : > > << > > itemNo > > supplier > > purchaseDate > > Price > > Qty > > << > > Please provide an idea if I want to get the latest 3 puchase records for > > each item and supplier. Thank you in advance. > select * from table_name where supplier=value order by purchaseDate desc > limit 3 > ??? > >
jack :
select * from purchase as p
where purchase_date >= ( select min(ppp.purchase_date)
from (select pp.purchase_date
from purchase as pp
where p.item_no = pp.item_no
and p.supplier = pp.supplier
order by 1 desc
limit 3 ) as ppp );
But this query have leak, if more than three purchases at day. For avoid
this leak your need unique row identifier. In attachement file with
test data and valid queries.
regards.
>Dima
>My question is that I want to produce ALL the lastest 3 records for EACH
>itemNo and supplier.
>
>Jack
>----- Original Message -----
>From: "dima" <_pppp@mail.ru>
>To: "jack" <datactrl@tpg.com.au>
>Cc: <pgsql-sql@postgresql.org>
>Sent: Monday, September 09, 2002 4:34 PM
>Subject: Re: [SQL] the best way to get the topest 3 record in every group
>
>
>
>
>>>There is a table like :
>>><<
>>>itemNo
>>>supplier
>>>purchaseDate
>>>Price
>>>Qty
>>><<
>>>Please provide an idea if I want to get the latest 3 puchase records for
>>>each item and supplier. Thank you in advance.
>>>
>>>
>>select * from table_name where supplier=value order by purchaseDate desc
>>limit 3
>>???
>>
>>
>>
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
>
>
>
Attachment
jack :
select * from purchase as p
where purchase_date >= ( select min(ppp.purchase_date) from (select pp.purchase_date
from purchase as pp where p.item_no = pp.item_no
and p.supplier = pp.supplier order by 1 desc
limit 3 ) as ppp );
But this query have leak, if more than three purchases at day. For avoid
this leak your need unique row identifier.
regards.
>Dima >My question is that I want to produce ALL the lastest 3 records for EACH >itemNo and supplier. > >Jack >-----
OriginalMessage ----- >From: "dima" <_pppp@mail.ru> >To: "jack" <datactrl@tpg.com.au> >Cc: <pgsql-sql@postgresql.org>
>Sent:Monday, September 09, 2002 4:34 PM >Subject: Re: [SQL] the best way to get the topest 3 record in every group > >
>> >>>There is a table like : >>><< >>>itemNo >>>supplier >>>purchaseDate >>>Price >>>Qty >>><< >>>Please provide an
ideaif I want to get the latest 3 puchase
records for >>>each item and supplier. Thank you in advance. >>> >>> >>select * from table_name where supplier=value
orderby purchaseDate desc >>limit 3 >>??? >> >> >> >> > > >---------------------------(end of
broadcast)--------------------------->TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > >
>>
jack :
select * from purchase as p
where purchase_date >= ( select min(ppp.purchase_date)
from (select pp.purchase_date
from purchase as pp
where p.item_no = pp.item_no
and p.supplier = pp.supplier
order by 1 desc
limit 3 ) as ppp );
But this query have leak, if more than three purchases at day. For avoid
this leak your need unique row identifier. In attachement file with
test data and valid queries.
regards.
>Dima
>My question is that I want to produce ALL the lastest 3 records for EACH
>itemNo and supplier.
>
>Jack
>----- Original Message -----
>From: "dima" <_pppp@mail.ru>
>To: "jack" <datactrl@tpg.com.au>
>Cc: <pgsql-sql@postgresql.org>
>Sent: Monday, September 09, 2002 4:34 PM
>Subject: Re: [SQL] the best way to get the topest 3 record in every group
>
>
>
>
>>>There is a table like :
>>><<
>>>itemNo
>>>supplier
>>>purchaseDate
>>>Price
>>>Qty
>>><<
>>>Please provide an idea if I want to get the latest 3 puchase records for
>>>each item and supplier. Thank you in advance.
>>>
>>>
>>select * from table_name where supplier=value order by purchaseDate desc
>>limit 3
>>???
>>
>>
>>
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
>
>
>
Attachment
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