Re: Extracting data where a column is max

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(at)narrowpathinc(dot)com>
To: "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)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
>