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

Re: Extracting data where a column is max

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Keith Worthington <keithw(at)narrowpathinc(dot)com>
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Extracting data where a column is max
Date: 2004-12-24 04:52:05
Message-ID: 20041224045205.GB89583@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-novice
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/

In response to

Responses

pgsql-novice by date

Next:From: brewDate: 2004-12-24 07:09:43
Subject: Re: Front end?
Previous:From: operationsengineer1Date: 2004-12-24 04:32:15
Subject: Re: Front end?

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