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

Re: query question

From: Sachin Srivastava <sachin(dot)srivastava(at)enterprisedb(dot)com>
To: pg noob <pgnube(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: query question
Date: 2011-09-21 06:15:43
Message-ID: 312CEA55-8D41-4AAD-891E-9CD4424859F0@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-novice
SELECT MAX(id) AS id , col1, MAX(col2) AS col2,  MAX(col3) AS col3  FROM tablename  GROUP BY col1

On Sep 16, 2011, at 7:00 PM, pg noob wrote:

> 
> 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.
> 
> 

--
Regards,
Sachin Srivastava
EnterpriseDB, the Enterprise PostgreSQL company.

In response to

pgsql-novice by date

Next:From: Asli AkarsakaryaDate: 2011-09-21 12:47:23
Subject: a spatial table's bounding box
Previous:From: Hany ABOU-GHOURYDate: 2011-09-21 04:29:39
Subject: Re: PG 9 adminstrations

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