Home > mailing lists

query question - Mailing list pgsql-novice

From	pg noob
Subject	query question
Date	September 21, 2011 02:14:57
Msg-id	CAPNY-2U9aQHp54gzw6nXN13kwjv2P1e76B=RnMyF2kvT9Qj7Vw@mail.gmail.com Whole thread
Responses	Re: query question Re: query question
List	pgsql-novice

Tree view

Hi all,

Is there an efficient way to select the set of rows which have the max values across multiple columns?

For example given this list of data,

id_    | col1        | col2            | col3
-------+-------------+------------------+-----------------
19657 |          10 | 1316114172563817 |               4
19656 |          10 | 1316114172563817 |               3
    24 |          12 | 1315847688545745 |               0
19644 |          13 | 1316114172563817 |               0
    26 |          14 | 1315847688545745 |               0
19646 |          15 | 1316114172563817 |               0
19582 |          15 | 1316112258713414 |               0
18269 |          15 | 1316023202508054 |               0
   199 |          15 | 1315936801616950 |               0
    37 |          15 | 1315847702117357 |               0
19648 |          16 | 1316114172563817 |               0
19583 |          16 | 1316112258713414 |               0
18272 |          16 | 1316023202508054 |               0
   202 |          16 | 1315936801616950 |               0
    38 |          16 | 1315847702117357 |               0
19652 |          17 | 1316114172563817 |               0
19585 |          17 | 1316112258713414 |               0
18276 |          17 | 1316023202508054 |               0
   206 |          17 | 1315936801616950 |               0
    39 |          17 | 1315847702117357 |               0

I would like to select the set of rows grouped by col1 which has first the highest value for col2 and second the highest value for col3.

The result set should include:

19657 |          10 | 1316114172563817 |               4
   24 |          12 | 1315847688545745 |               0
19644 |          13 | 1316114172563817 |               0
   26 |          14 | 1315847688545745 |               0
19646 |          15 | 1316114172563817 |               0
19648 |          16 | 1316114172563817 |               0
19652 |          17 | 1316114172563817 |               0

Thank you.

pgsql-novice by date:

From: Eric Hulburd
Date: 15 September 2011, 14:28:34
Subject: can't connect to server

From: Sachin Srivastava
Date: 21 September 2011, 03:16:01
Subject: Re: query question

query question - Mailing list pgsql-novice

Previous

Next