Re: Extracting data where a column is max

Hi George,

Thanks for the idea. Unfortunately it does not provide the results that I am
looking for.

IPADB=# 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;
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 only want the rows associated with the largest (latest) inventory_id. This
is the result I am trying to get.
inventory_id | item_id | quantity
--------------+------------+----------
1 | RPP3S114BK | 629
1 | TW360PYSD | 444
1 | TW360PYWH | 910
6 | 004173-1 | 44
6 | RMFPB14BK | 399
9 | RPP3S14YL | 50

Keith

> 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
> >

Kind Regards,
Keith Worthington
President

Narrow Path, Inc.
520 Trumbull Highway
Lebanon, CT 06249-1424
Telephone: (860) 642-7114
Facsimile: (860) 642-7290
Mobile: (860) 608-6101

______________________________________________
99main Internet Services http://www.99main.com