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

Re: Extracting data where a column is max

From: George Weaver <gweaver(at)shaw(dot)ca>
To: KeithW(at)narrowpathinc(dot)com,PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Extracting data where a column is max
Date: 2004-12-23 21:42:37
Message-ID: 00a801c4e938$570ee1d0$6400a8c0@Dell4500 (view raw or flat)
Thread:
Lists: pgsql-novice
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
> 



In response to

Responses

pgsql-novice by date

Next:From: Afton & Ray StillDate: 2004-12-23 21:53:23
Subject: Re: Front end?
Previous:From: Keith WorthingtonDate: 2004-12-23 20:57:46
Subject: Extracting data where a column is max

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