From: Viacheslav N Tararin <taras@dekasoft.com.ua>
To: jack <datactrl@tpg.com.au>
To: pgsql-sql@postgresql.org
Subject: Re: the best way to get the topest 3 record in every group
Date: Mon, 09 Sep 2002 15:23:41 +0300
Message-ID: <3D7C92CD.4010908@dekasoft.com.ua> (raw)
References: <002201c25796$d923d3f0$1400a8c0@jac>
<3D7C4108.70407@mail.ru>
<000701c257d8$1163c9a0$1400a8c0@jac>
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" <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
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: taras@dekasoft.com.ua, datactrl@tpg.com.au
Subject: Re: the best way to get the topest 3 record in every group
In-Reply-To: <3D7C92CD.4010908@dekasoft.com.ua>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox