Re: query question

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.