Re: Extracting data where a column is max

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/