Re: Extracting data where a column is max - Mailing list pgsql-novice

Hi Keith,

Can you not just add inventory.tbl_data.quantity to the columns retrieved by
your select statement or am I missing something?

SELECT max(inventory.tbl_data.inventory_id) AS inventory_id,
                     inventory.tbl_data.item_id,
                     inventory.tbl_data.quantity
         FROM inventory.tbl_data
 GROUP BY inventory.tbl_data.item_id,
                     inventory.tbl_data.quantity
 ORDER BY inventory_id, inventory.tbl_data.item_id;

Regards,
George

----- Original Message -----
From: "Keith Worthington" <keithw@narrowpathinc.com>
To: "PostgreSQL Novice" <pgsql-novice@postgresql.org>
Sent: Thursday, December 23, 2004 2:57 PM
Subject: [NOVICE] Extracting data where a column is max


> Hi All,
>
> 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.
> IPADB=# SELECT max(inventory.tbl_data.inventory_id) AS inventory_id,
> inventory.tbl_data.item_id  FROM inventory.tbl_data GROUP BY
> inventory.tbl_data.item_id ORDER BY inventory_id,
> inventory.tbl_data.item_id;
> inventory_id |  item_id
> --------------+------------
>            1 | RPP3S114BK
>            1 | TW360PYSD
>            1 | TW360PYWH
>            6 | 004173-1
>            6 | RMFPB14BK
>            9 | RPP3S14YL
> (6 rows)
>
> But how do I get the corresponding quantity for each record?
>
> TIA
>
> BTW The SQL code to create the table and data is below.  (Are ya proud of
> me
> Michael? ;-) )
>
> Kind Regards,
> Keith
>
> --
> -- PostgreSQL database dump
> --
>
> --
> -- Name: inventory; Type: SCHEMA; Schema: -; Owner: postgres
> --
>
> CREATE SCHEMA inventory;
>
> --
> -- Name: tbl_data; Type: TABLE; Schema: inventory; Owner: postgres
> --
>
> CREATE TABLE tbl_data (
>    inventory_id integer NOT NULL,
>    item_id character varying(20) NOT NULL,
>    quantity real NOT NULL
> );
>
> --
> -- Name: tbl_data; Type: TABLE DATA; Schema: inventory; Owner: postgres
> --
>
> INSERT INTO tbl_data VALUES (1, 'RMFPB14BK', 551);
> INSERT INTO tbl_data VALUES (1, 'RPP3S114BK', 629);
> INSERT INTO tbl_data VALUES (1, 'RPP3S14YL', 1009);
> INSERT INTO tbl_data VALUES (1, 'TW360PYSD', 444);
> INSERT INTO tbl_data VALUES (1, 'TW360PYWH', 910);
> INSERT INTO tbl_data VALUES (6, '004173-1', 44);
> INSERT INTO tbl_data VALUES (6, 'RMFPB14BK', 399);
> INSERT INTO tbl_data VALUES (6, 'RPP3S14YL', 1233);
> INSERT INTO tbl_data VALUES (9, 'RPP3S14YL', 50);
>
> --
> -- Name: tbl_data_pkey; Type: CONSTRAINT; Schema: inventory; Owner:
> postgres
> --
>
> ALTER TABLE ONLY tbl_data
>    ADD CONSTRAINT tbl_data_pkey PRIMARY KEY (inventory_id, item_id);
>
> --
> -- Name: TABLE tbl_data; Type: COMMENT; Schema: inventory; Owner: postgres
> --
>
> COMMENT ON TABLE tbl_data IS 'Contains the total count data.';
>
> ______________________________________________
> 99main Internet Services http://www.99main.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>