Re: Extracting data where a column is max

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