CODEWITHSUNDEEP
sqlsortingdb2
inetphantom
inetphantom

Reputation: 2617

Groupwise sorting with SQL

I need to sort data groupwise. For my knowledge this can not be achieved with sort by multiple conditions and also a group does not what I need, as I don't want to reduce the records but sort them while keeping groups together. Look at the following data:

Group Data1 Data2 
a      1      5       
a      2      4       
a      3      7       
a      4      6       
b      1      9       
b      2      2       
b      3      1       
b      4     13       
c      1      8       
c      2     10       
c      3     13       
c      4     12

I want the groups to stay together, then sort by Data2 desc and then by Data1 desc. To keep them together, after the first record of any group follows the rest with the same group, sorted the same way. Think of it that the group gets sorted by max Data2 desc,max Data1 desc instead of alphabetically.

Group Data1 Data2
b      4     13  
b      1      9  
b      2      2  
b      3      1  
c      3     13  
c      4     12  
c      2     10  
c      1      8  
a      3      7  
a      4      6  
a      1      5  
a      2      4

Can I achieve this with SQL?

In an other environment I could define a comperator and then simply sort by Group, Data2, Data1.

Upvotes: 1

Views: 312

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

Use analytic functions in the ORDER BY clause:

select *
from mytable
order by
  max(data2) over (partition by "Group") desc,
  max(data1) over (partition by "Group") desc,
  "Group",
  data2 desc,
  data1 desc;

Upvotes: 4

Mark Barinstein
Mark Barinstein

Reputation: 12314

Try this:

with a (Group, Data1, Data2 ) as (values
  ('a', 1, 5)       
, ('a', 2, 4)       
, ('a', 3, 7)       
, ('a', 4, 6)       
, ('b', 1, 9)       
, ('b', 2, 2)       
, ('b', 3, 1)       
, ('b', 4, 13)       
, ('c', 1, 8)       
, ('c', 2, 10)       
, ('c', 3, 13)       
, ('c', 4, 12)      
)
select a.*
from (
select b.*, rownumber() over(order by data2 desc, data1 desc) rn2
from (
select a.*, rownumber() over (partition by group order by data2 desc, data1 desc) rn1
from a
) b
where rn1=1
) b 
join a on a.group=b.group
order by b.rn2, a.data2 desc, a.data1 desc;

Upvotes: 0

Related Questions