Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A35E1475B8D for ; Mon, 9 Sep 2002 08:26:32 -0400 (EDT) Received: from dekasoft.com.ua (unknown [193.201.199.5]) by postgresql.org (Postfix) with ESMTP id E6A6E475AE5 for ; Mon, 9 Sep 2002 08:26:29 -0400 (EDT) Received: from dekasoft.com.ua (unknown [192.168.1.9]) by dekasoft.com.ua (Postfix) with ESMTP id 89292CF66; Mon, 9 Sep 2002 15:26:24 +0300 (EEST) Message-ID: <3D7C92CD.4010908@dekasoft.com.ua> Date: Mon, 09 Sep 2002 15:23:41 +0300 From: Viacheslav N Tararin User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; ru-RU; rv:1.0.0) Gecko/20020526 X-Accept-Language: uk, ru, en-us MIME-Version: 1.0 To: jack , pgsql-sql@postgresql.org Subject: Re: the best way to get the topest 3 record in every group References: <002201c25796$d923d3f0$1400a8c0@jac> <3D7C4108.70407@mail.ru> <000701c257d8$1163c9a0$1400a8c0@jac> Content-Type: text/plain; charset=KOI8-R; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/37 X-Sequence-Number: 9314 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@mail.ru> >To: "jack" >Cc: >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 > > > >