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