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

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


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