Re: the best way to get the topest 3 record in every group

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
>----- Original Message -----
>From: "dima" <_pppp(at)mail(dot)ru>
>To: "jack" <datactrl(at)tpg(dot)com(dot)au>
>Cc: <pgsql-sql(at)postgresql(dot)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
>
>
>
>