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

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