Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3AE80475AD7 for ; Mon, 9 Sep 2002 05:38:30 -0400 (EDT) Received: from dekasoft.com.ua (unknown [193.201.199.5]) by postgresql.org (Postfix) with ESMTP id E04D0475ADE for ; Mon, 9 Sep 2002 05:38:25 -0400 (EDT) Received: from dekasoft.com.ua (unknown [192.168.1.9]) by dekasoft.com.ua (Postfix) with ESMTP id 39F00CF65; Mon, 9 Sep 2002 12:38:20 +0300 (EEST) Message-ID: <3D7C6B69.8010601@dekasoft.com.ua> Date: Mon, 09 Sep 2002 12:35:37 +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: multipart/mixed; boundary="------------000600000006080303040801" X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/36 X-Sequence-Number: 9313 This is a multi-part message in MIME format. --------------000600000006080303040801 Content-Type: text/plain; charset=KOI8-R; format=flowed Content-Transfer-Encoding: 7bit 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" >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 > > > > --------------000600000006080303040801 Content-Type: application/x-zip-compressed; name="sqls.zip" Content-Transfer-Encoding: base64 Content-Disposition: inline; filename="sqls.zip" UEsDBBQAAAAIAMFjKS3U+re5iAEAAEkPAAANACQAdGVzdF9kYXRhLnNxbAoA IAAAAAAAAQAYAADJDnnjV8IBAIjGsXpXwgHAmfxI4FfCAb3V0W6DIBQG4HsT 34G72oQsnAPttuxhGlfJZuKqQ12ytx/oSibqbINoTC+w5VP5y5+psiJN+lpI UrXq/J7W8iWO4uisZNpI5wpJ4ojoI2/kx+lSkvzSyDepKOmH67aqilwqd/z6 81NmpjQf9orKz9J+vR/7bL6vI3G0724mv9RSNWa0tJMlvzdBLUuHEO1np2bC PflKi1bWJAFK9LlDxvCBgT53ZgDAQAEZ1AxSxMAM1wynnAdmhGYEFWL11cG/ EPargwAUV1+gaUkvEWCIp0LXAsYYNR/rP9fAwt4CYwV4h1MWGmvtpA8sbi1u rLXjPrCEtYSx1s88bJgO1wqZjhkrSDpgw3TAcjomShXmWxVsrWakO4Z9uHXZ Tr8sSPKMEp/6uKt2fTgcc0v168PxMbdUwz6cGHPLdewDHrq831vLPuJxTlyo Zx/zsTPv34h9zKexedOG7GM+z5r/b8xe/3/moLfu0F4oOOjtRe7F9sndNkjA x2jwJIGYRQNG6eCgm0Tp6KBTUfoBUEsDBBQAAAAIAAxkKS0pGsPjuAAAAFkD AAAHACQAc3FsLnNxbAoAIAAAAAAAAQAYAABEss3jV8IBAIjGsXpXwgGAT0i4 4VfCAdWRQWrDMBBF9wbd4S/tUgxOl8W9SlCsCR6w5EGyCbl97FhJUGiLSrup diP0/tfwAg3UTXjB0Y8WMvuu14GgA0QVp5483S/3Rk+EjxYlImXZlSJSJy8q VeDrc60pI/+MfksmCclHJYeLu9Q8kd27Ee3aHoesXmhnFj7MIgOT3wJuU07C 6M2CHc5oYCh0yGEGtjzhDdW2qKB6V4UqPrfWpNpqNmCHh65VFZsf6WHz+geW mn+pafcrTRdQSwECGQAUAAAACADBYykt1Pq3uYgBAABJDwAADQAAAAAAAAAA ACAAAAAAAAAAdGVzdF9kYXRhLnNxbFBLAQIZABQAAAAIAAxkKS0pGsPjuAAA AFkDAAAHAAAAAAAAAAAAIAAAANcBAABzcWwuc3FsUEsFBgAAAAACAAIAcAAA ANgCAAAAAA== --------------000600000006080303040801--