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 14:48:41 +0300
Message-ID: <3D7C8A99.1070204@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. 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
>
>
>
>
Attachments:
sqls.zip (application/x-zip-compressed, 862B) download
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: <3D7C8A99.1070204@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