Postgres Pro
Downloads
Home   >   mailing lists

query question - Mailing list pgsql-novice

From pg noob
Subject query question
Date
Msg-id CAPNY-2U9aQHp54gzw6nXN13kwjv2P1e76B=RnMyF2kvT9Qj7Vw@mail.gmail.com
Whole thread Raw
Responses Re: query question  (Sachin Srivastava <sachin.srivastava@enterprisedb.com>)
Re: query question  (Tom Lane <tgl@sss.pgh.pa.us>)
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:

Previous
From: Eric Hulburd
Date:
Subject: can't connect to server
Next
From: Sachin Srivastava
Date:
Subject: Re: query question