Thread: 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
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
>
On Thu, Dec 23, 2004 at 03:57:46PM -0500, Keith Worthington wrote:
> 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.
If you don't mind using a non-standard construct then you could use
SELECT DISTINCT ON. For more info see the "SELECT" and "Select
Lists" documentation.
SELECT DISTINCT ON (item_id) *
FROM tbl_data
ORDER BY item_id, inventory_id DESC;
inventory_id | item_id | quantity
--------------+------------+----------
6 | 004173-1 | 44
6 | RMFPB14BK | 399
1 | RPP3S114BK | 629
9 | RPP3S14YL | 50
1 | TW360PYSD | 444
1 | TW360PYWH | 910
(6 rows)
The ORDER BY specification is important. If you need a different
order in the final result then you can use a sub-select:
SELECT * FROM (
SELECT DISTINCT ON (item_id) *
FROM tbl_data
ORDER BY item_id, inventory_id DESC
) AS s
ORDER BY inventory_id, item_id;
inventory_id | item_id | quantity
--------------+------------+----------
1 | RPP3S114BK | 629
1 | TW360PYSD | 444
1 | TW360PYWH | 910
6 | 004173-1 | 44
6 | RMFPB14BK | 399
9 | RPP3S14YL | 50
(6 rows)
> BTW The SQL code to create the table and data is below. (Are ya proud of me
> Michael? ;-) )
:-)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
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
Hi Michael,
I ended up with this query as I do not really care about the inventory_id in
the end game. What about SELECT DISTINCT is non-standard? Are there any
implications of using this other than portability?
SELECT DISTINCT ON ( inventory.tbl_data.item_id )
inventory.tbl_data.item_id,
inventory.tbl_data.quantity
FROM inventory.tbl_data
ORDER BY inventory.tbl_data.item_id,
inventory.tbl_data.inventory_id DESC;
Kind Regards,
Keith
> On Thu, Dec 23, 2004 at 03:57:46PM -0500, Keith Worthington wrote:
>
> > 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.
>
> If you don't mind using a non-standard construct then you could use
> SELECT DISTINCT ON. For more info see the "SELECT" and "Select
> Lists" documentation.
>
> SELECT DISTINCT ON (item_id) *
> FROM tbl_data
> ORDER BY item_id, inventory_id DESC;
>
> inventory_id | item_id | quantity
> --------------+------------+----------
> 6 | 004173-1 | 44
> 6 | RMFPB14BK | 399
> 1 | RPP3S114BK | 629
> 9 | RPP3S14YL | 50
> 1 | TW360PYSD | 444
> 1 | TW360PYWH | 910
> (6 rows)
>
> The ORDER BY specification is important. If you need a different
> order in the final result then you can use a sub-select:
>
> SELECT * FROM (
> SELECT DISTINCT ON (item_id) *
> FROM tbl_data
> ORDER BY item_id, inventory_id DESC
> ) AS s
> ORDER BY inventory_id, item_id;
>
> inventory_id | item_id | quantity
> --------------+------------+----------
> 1 | RPP3S114BK | 629
> 1 | TW360PYSD | 444
> 1 | TW360PYWH | 910
> 6 | 004173-1 | 44
> 6 | RMFPB14BK | 399
> 9 | RPP3S14YL | 50
> (6 rows)
>
> > BTW The SQL code to create the table and data is below. (Are ya proud of me
> > Michael? ;-) )
>
> :-)
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
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
On Mon, Dec 27, 2004 at 12:18:53 -0500, Keith Worthington <keithw@narrowpathinc.com> wrote: > Hi Michael, > > I ended up with this query as I do not really care about the inventory_id in > the end game. What about SELECT DISTINCT is non-standard? Are there any > implications of using this other than portability? It's the "ON" clause that is nonstandard. If you aren't worried about portability than there isn't a problem with using that feature.