Skip site navigation (1) Skip section navigation (2)

Extracting data where a column is max

From: "Keith Worthington" <keithw(at)narrowpathinc(dot)com>
To: "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Extracting data where a column is max
Date: 2004-12-23 20:57:46
Message-ID: 20041223205746.M81321@narrowpathinc.com (view raw or flat)
Thread:
Lists: 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


Responses

pgsql-novice by date

Next:From: George WeaverDate: 2004-12-23 21:42:37
Subject: Re: Extracting data where a column is max
Previous:From: Tom LaneDate: 2004-12-22 16:37:42
Subject: Re: Encoding problems

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group