sql - Order entire groups of results by highest value in each group -

for example, let's have table in postgresql (higher 9.0), filled data:

row_id    percent    isrc 1         100        123iee43 2         100        1234wr32 3         98         123iee43 4         99         1234wr32 5         95         12313be3 6         99         12313be3 7         96         12313be3 

i result contain of above rows grouped column isrc , entire groups ordered percent, descending. result should like:

row_id    percent    isrc 1         100        123iee43 3         98         123iee43 2         100        1234wr32 4         99         1234wr32 6         99         12313be3 7         96         12313be3 5         95         12313be3 

if wanted ascending order, i'd expect (i want order first row in 1 group, other rows in single group not matter):

row_id    percent    isrc 6         99         12313be3 7         96         12313be3 5         95         12313be3 1         100        123iee43 3         98         123iee43 2         100        1234wr32 4         99         1234wr32 

i guess must use window functions somehow unable find correct solution if 1 exists. also, neat if solution elegant possible. :)

window function

select row_id, percent, isrc   tbl order  max(percent) over(partition isrc) desc, isrc, percent desc; 

the aggregate function max() can used window function. don't use order by in window clause because, per documentation:

when aggregate function used window function, aggregates on rows within current row's window frame. aggregate used order by , default window frame definition produces "running sum" type of behavior, may or may not what's wanted. obtain aggregation on whole partition, omit order by or use rows between unbounded preceding , unbounded following. other frame specifications can used obtain other effects.

window function cannot used in where or having clause, because applied before window function. can use 1 in order by clause, applied last (even after distinct, before limit).

window functions can expensive, 1 simplifies query much, may faster alternatives.
, elegant.

aggregate function

plus join. may or may not faster.

select row_id, percent, isrc   tbl join  (select isrc, max(percent) max_pct tbl group 1) x using (isrc) order  x.max_pct desc, isrc, percent desc; 

distinct on

very similar using aggregate function.

select t.*   tbl t join  (     select distinct on (isrc) isrc, percent       tbl     order  isrc, percent desc     ) s using (isrc) order s.percent desc, s.isrc, t.percent desc 

you don't need window function here.

sql fiddle demonstrating of above.