Обсуждение: Extracting data where a column is max

Поиск
Список
Период
Сортировка

Extracting data where a column is max

От
"Keith Worthington"
Дата:
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

Re: Extracting data where a column is max

От
George Weaver
Дата:
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
>

Re: Extracting data where a column is max

От
Michael Fuhr
Дата:
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/

Re: Extracting data where a column is max

От
"Keith Worthington"
Дата:
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

Re: Extracting data where a column is max

От
"Keith Worthington"
Дата:
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

Re: Extracting data where a column is max

От
Bruno Wolff III
Дата:
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.