I don't exactly know how to phrase this question but I was wondering how to achieve the output displayed below using sql:

Input Table:

Output/Selected Table:

to explain what happened to the order it first sorted by ColA ascending. After this it then ordered each section based off of the count of times colB repeated in that section with the most repeated item being put ahead of the least repeated ones.

My problem is that I can't figure out how to complete the second ordering without taking the count of all repeats rather than just the ones in its given section.

here is my current code: order by ColA, count(*) over (partition by ColB) desc;

CodePudding user response：

If you don't also partition by ColA the counts won't be based on the ordering of ColA.

order by ColA, count(*) over (partition by colA, ColB) desc;

provides your desired result.

CodePudding user response：

You don't mention the database so I wrote a solution for PostgreSQL. You can do:

select t.*
from t
join (
  select cola, colb, 
    row_number() over(partition by cola order by count(*) desc) as rn
  from t
  group by cola, colb
) x on x.cola = t.cola and x.colb = t.colb
order by t.cola, x.rn

Result:

 cola  colb 
 ----- ---- 
 1     2    
 1     2    
 1     2    
 1     1    
 1     1    
 2     1    
 2     1    
 2     1    
 2     2    

See running example at db<>fiddle.