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