The table is :-

This is my table service. And I want to know the possible simplest query to find the maximum in each SQL group and the number of maximums in that group...

Display the SQL group name(in this case it is position), and the number of occurences of the maximum value of that group in that group

Expected Output:-

CodePudding user response：

One option is the window function max() over()

Example

Select POSITION
      ,sum(Cnt) as No_of_Max 
 From (
        Select * 
              ,case when EXPERIENCE=max(EXPERIENCE) over (partition by POSITION) then 1 else 0 end as Cnt
         From YourTable
      ) A
Group By POSITION

Results

POSITION    No_of_Max
bottom      2
left        1
right       1
top         3

CodePudding user response：

As of Oracle, analytic function helps.

Sample data:

SQL> with test (position, experience) as
  2    (select 'top'   ,  90 from dual union all
  3     select 'bottom', 100 from dual union all
  4     select 'top'   ,  90 from dual union all
  5     select 'left'  ,  90 from dual union all
  6     select 'right' , 100 from dual union all
  7     select 'top'   ,  90 from dual union all
  8     select 'left'  ,  80 from dual union all
  9     select 'top'   ,  80 from dual union all
 10     select 'bottom', 100 from dual union all
 11     select 'left'  , 100 from dual
 12    ),

Query begins here: rank analytic function ranks rows by number of occurrences, while the final select returns rows which rank as the highest:

 13  temp as
 14    (select position, experience, count(*) no_of_maximums,
 15       rank() over (partition by position order by count(*) desc) rn
 16     from test
 17     group by position, experience
 18    )
 19  select position, no_of_maximums
 20  from temp
 21  where rn = 1;

POSITION   NO_OF_MAXIMUMS
---------- --------------
bottom                  2
left                    1
right                   1
top                     3

SQL>