pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedthe best way to get the topest 3 record in every group
7+ messages / 4 participants
[nested] [flat]
* the best way to get the topest 3 record in every group
@ 2002-09-09 00:21 jack <datactrl@tpg.com.au>
0 siblings, 1 reply; 7+ messages in thread
From: jack @ 2002-09-09 00:21 UTC (permalink / raw)
To: pgsql-sql
Hi,
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.
jack
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: the best way to get the topest 3 record in every group
@ 2002-09-09 06:34 dima <_pppp@mail.ru>
parent: jack <datactrl@tpg.com.au>
0 siblings, 1 reply; 7+ messages in thread
From: dima @ 2002-09-09 06:34 UTC (permalink / raw)
To: jack <datactrl@tpg.com.au>; +Cc: pgsql-sql
> 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
???
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: the best way to get the topest 3 record in every group
@ 2002-09-09 08:08 jack <datactrl@tpg.com.au>
parent: dima <_pppp@mail.ru>
0 siblings, 4 replies; 7+ messages in thread
From: jack @ 2002-09-09 08:08 UTC (permalink / raw)
To: dima <_pppp@mail.ru>; +Cc: pgsql-sql
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
> ???
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: the best way to get the topest 3 record in every group
@ 2002-09-09 09:35 Viacheslav N Tararin <taras@dekasoft.com.ua>
parent: jack <datactrl@tpg.com.au>
3 siblings, 0 replies; 7+ messages in thread
From: Viacheslav N Tararin @ 2002-09-09 09:35 UTC (permalink / raw)
To: jack <datactrl@tpg.com.au>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: the best way to get the topest 3 record in every group
@ 2002-09-09 11:48 Viacheslav N Tararin <taras@dekasoft.com.ua>
parent: jack <datactrl@tpg.com.au>
3 siblings, 0 replies; 7+ messages in thread
From: Viacheslav N Tararin @ 2002-09-09 11:48 UTC (permalink / raw)
To: jack <datactrl@tpg.com.au>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: the best way to get the topest 3 record in every group
@ 2002-09-09 12:23 Viacheslav N Tararin <taras@dekasoft.com.ua>
parent: jack <datactrl@tpg.com.au>
3 siblings, 0 replies; 7+ messages in thread
From: Viacheslav N Tararin @ 2002-09-09 12:23 UTC (permalink / raw)
To: jack <datactrl@tpg.com.au>; pgsql-sql
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
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: the best way to get the topest 3 record in every group
@ 2002-09-09 16:23 Masaru Sugawara <rk73@sea.plala.or.jp>
parent: jack <datactrl@tpg.com.au>
3 siblings, 0 replies; 7+ messages in thread
From: Masaru Sugawara @ 2002-09-09 16:23 UTC (permalink / raw)
To: jack <datactrl@tpg.com.au>; +Cc: pgsql-sql
On Mon, 9 Sep 2002 18:08:21 +1000
"jack" <datactrl@tpg.com.au> wrote:
> Dima
> My question is that I want to produce ALL the lastest 3 records for EACH
> itemNo and supplier.
>
> Jack
I often use the next query for ranking, which is equivalent to Oracle's
PARTITION BY(). It isn't influenced by what kind of data your table has;
it only depends on the result of sorting
CREATE TEMP SEQUENCE seq_purchase;
SELECT t5.item_no, t5.supplier, t5.purchase_date,
t5.price, t5.qty, t5.i - t3.n + 1 AS rank
FROM (SELECT t2.item_no, t2.supplier, min(t2.i) AS n
FROM (SELECT t1.*, nextval('seq_purchase') - 1 AS i
FROM (SELECT (SELECT setval('seq_purchase',1)), *
FROM purchase
ORDER BY item_no, supplier, purchase_date desc
) AS t1
LIMIT ALL
) AS t2
GROUP BY t2.item_no, t2.supplier
) AS t3,
(SELECT t4.*, nextval('seq_purchase') - 1 AS i
FROM (SELECT (SELECT setval('seq_purchase',1)), *
FROM purchase
ORDER BY item_no, supplier, purchase_date DESC
) AS t4
LIMIT ALL
) AS t5
WHERE t3.item_no = t5.item_no
AND t3.supplier = t5.supplier
AND t5.i - t3.n + 1 <= 3
;
Regards,
Masaru Sugawara
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2002-09-09 16:23 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-09 00:21 the best way to get the topest 3 record in every group jack <datactrl@tpg.com.au>
2002-09-09 06:34 ` dima <_pppp@mail.ru>
2002-09-09 08:08 ` jack <datactrl@tpg.com.au>
2002-09-09 09:35 ` Viacheslav N Tararin <taras@dekasoft.com.ua>
2002-09-09 11:48 ` Viacheslav N Tararin <taras@dekasoft.com.ua>
2002-09-09 12:23 ` Viacheslav N Tararin <taras@dekasoft.com.ua>
2002-09-09 16:23 ` Masaru Sugawara <rk73@sea.plala.or.jp>
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