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. 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(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
>
>
>
>