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

query question

From: pg noob <pgnube(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: query question
Date: 2011-09-16 13:30:40
Message-ID: CAPNY-2U9aQHp54gzw6nXN13kwjv2P1e76B=RnMyF2kvT9Qj7Vw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.

Responses

pgsql-novice by date

Next:From: Hany ABOU-GHOURYDate: 2011-09-21 03:57:44
Subject: PG 9 adminstrations
Previous:From: Eric HulburdDate: 2011-09-15 17:28:26
Subject: can't connect to server

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