Unix Technical Forum

Extracting data where a column is max

This is a discussion on Extracting data where a column is max within the pgsql Novice forums, part of the PostgreSQL category; --> Hi All, I have the following data: IPADB=# SELECT * FROM inventory.tbl_data; inventory_id | item_id | quantity --------------+------------+---------- 1 ...


Go Back   Unix Technical Forum > Database Server Software > PostgreSQL > pgsql Novice

Extracting data where a column is max Extracting data where a column is max

Register FAQ Members List Calendar Search Today's Posts Mark Forums Read

Reply

 

LinkBack Thread Tools Display Modes
  #1 (permalink)  
Old 04-17-2008, 08:17 PM
Keith Worthington
 
Posts: n/a
Default 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

Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #2 (permalink)  
Old 04-17-2008, 08:17 PM
George Weaver
 
Posts: n/a
Default Re: Extracting data where a column is max
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
>



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #3 (permalink)  
Old 04-17-2008, 08:17 PM
Michael Fuhr
 
Posts: n/a
Default Re: Extracting data where a column is max
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/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #4 (permalink)  
Old 04-17-2008, 08:17 PM
Keith Worthington
 
Posts: n/a
Default 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@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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #5 (permalink)  
Old 04-17-2008, 08:17 PM
Keith Worthington
 
Posts: n/a
Default Re: Extracting data where a column is max
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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #6 (permalink)  
Old 04-17-2008, 08:17 PM
Bruno Wolff III
 
Posts: n/a
Default Re: Extracting data where a column is max
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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Reply

« Rewritten queries | Calculating the difference between result columns »

Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On
Forum Jump


All times are GMT. The time now is 05:35 AM.

Contact Us - Unix Technical Forums - Top

Powered by vBulletin® Version 3.6.5
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
SEO by vBSEO 3.2.0
www.UnixAdminTalk.com