Обсуждение: query question
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.
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.
pg noob <pgnube@gmail.com> writes: > Is there an efficient way to select the set of rows which have the max > values across multiple columns? I think SELECT DISTINCT ON might help you. The idea is to sort the rows in an appropriate order then take the first row from each group. Look at the "weather reports" example in PG's SELECT reference page for inspiration. regards, tom lane