Skip site navigation (1) Skip section navigation (2)

Re: Extracting data where a column is max

From: "Keith Worthington" <keithw(at)narrowpathinc(dot)com>
To: Michael Fuhr <mike(at)fuhr(dot)org>
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Extracting data where a column is max
Date: 2004-12-27 17:18:53
Message-ID: 20041227171853.M70274@narrowpathinc.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi Michael,

I ended up with this query as I do not really care about the inventory_id in
the end game.  What about SELECT DISTINCT is non-standard?  Are there any
implications of using this other than portability?

SELECT DISTINCT ON ( inventory.tbl_data.item_id )
       inventory.tbl_data.item_id,
       inventory.tbl_data.quantity
  FROM inventory.tbl_data
 ORDER BY inventory.tbl_data.item_id,
          inventory.tbl_data.inventory_id DESC;

Kind Regards,
Keith

> On Thu, Dec 23, 2004 at 03:57:46PM -0500, Keith Worthington wrote:
> 
> > I have the following data:
> > IPADB=# SELECT * FROM inventory.tbl_data;
> >  inventory_id |  item_id   | quantity 
> > --------------+------------+----------
> >             1 | RMFPB14BK  |      551
> >             1 | RPP3S114BK |      629
> >             1 | RPP3S14YL  |     1009
> >             1 | TW360PYSD  |      444
> >             1 | TW360PYWH  |      910
> >             6 | 004173-1   |       44
> >             6 | RMFPB14BK  |      399
> >             6 | RPP3S14YL  |     1233
> >             9 | RPP3S14YL  |       50
> > (9 rows)
> > 
> > I want to retrieve the item_id and the quantity corresponding to the maximum
> > inventory_id.  I can get the proper item_id.
> 
> If you don't mind using a non-standard construct then you could use
> SELECT DISTINCT ON.  For more info see the "SELECT" and "Select
> Lists" documentation.
> 
> SELECT DISTINCT ON (item_id) *
> FROM tbl_data
> ORDER BY item_id, inventory_id DESC;
> 
>  inventory_id |  item_id   | quantity 
> --------------+------------+----------
>             6 | 004173-1   |       44
>             6 | RMFPB14BK  |      399
>             1 | RPP3S114BK |      629
>             9 | RPP3S14YL  |       50
>             1 | TW360PYSD  |      444
>             1 | TW360PYWH  |      910
> (6 rows)
> 
> The ORDER BY specification is important.  If you need a different
> order in the final result then you can use a sub-select:
> 
> SELECT * FROM (
>   SELECT DISTINCT ON (item_id) *
>   FROM tbl_data
>   ORDER BY item_id, inventory_id DESC
> ) AS s
> ORDER BY inventory_id, item_id;
> 
>  inventory_id |  item_id   | quantity 
> --------------+------------+----------
>             1 | RPP3S114BK |      629
>             1 | TW360PYSD  |      444
>             1 | TW360PYWH  |      910
>             6 | 004173-1   |       44
>             6 | RMFPB14BK  |      399
>             9 | RPP3S14YL  |       50
> (6 rows)
> 
> > BTW The SQL code to create the table and data is below.  (Are ya proud of me
> > Michael? ;-) )
> 
> :-)
> 
> -- 
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/


Kind Regards,
Keith Worthington
President

Narrow Path, Inc.
520 Trumbull Highway
Lebanon, CT 06249-1424
Telephone:  (860) 642-7114
Facsimile:  (860) 642-7290
Mobile:     (860) 608-6101

______________________________________________
99main Internet Services http://www.99main.com


In response to

Responses

pgsql-novice by date

Next:From: sarlav kumarDate: 2004-12-27 17:32:33
Subject: user defined data type problem while dumping?
Previous:From: Keith WorthingtonDate: 2004-12-27 16:59:49
Subject: Re: Extracting data where a column is max

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group