CODEWITHSUNDEEP
sqloracle-databasesubquery
Soren
Soren

Reputation: 813

Oracle Top Max Count Results Sql Query

I have an Oracle query that counts the number of times something appears along with the grouped by details. It is similar to this:

SELECT COUNT(1) AS Num_Found, Column_A, Column_B, Column_C
FROM Some_Table
GROUP BY Column_A, Column_B, Column_C

I get result someting like this

|-----------|----------|----------|----------|
| Num_Found | Column_A | Column_B | Column_C |
| 145       | Acct1    | SubAcct1 | XXXX     |
| 6         | Acct1    | SubAcct1 | yyyy     |
| 346       | Acct2    | SubAcct2 | qwert    |
| 97        | Acct2    | SubAcct2 | plkmn    |
|-----------|----------|----------|----------|

I am not sure how to get Column_A, Column_B, Column_C based on the max count of Num_Found. So that I can get a result like this:

|-----------|----------|----------|----------|
| Num_Found | Column_A | Column_B | Column_C |
| 145       | Acct1    | SubAcct1 | XXXX     |
| 346       | Acct2    | SubAcct2 | qwert    |
|-----------|----------|----------|----------|

Upvotes: 0

Views: 258

Answers (3)

Sun21
Sun21

Reputation: 34

Try This. Based on the output you want, looks like you want to partition the result by column_a

select num_found, column_a, column_b, column_c
from ( select a.* , max(num_found) over (partition by column_a)as max_num from table a) where num=max_num

Upvotes: 0

Kim Berg Hansen
Kim Berg Hansen

Reputation: 2019

You group by three columns and of the resulting groups, you only wish to keep the one with the highest count for each combination of two of the columns?

It could be done with something like:

select num_found, column_a, column_b, column_c
from (
  SELECT COUNT(*) AS Num_Found, Column_A, Column_B, Column_C
       , row_number() over (
            partition by column_a, column_b
            order by count(*) desc
         ) rn
  FROM Some_Table
  GROUP BY Column_A, Column_B, Column_C
)
where rn = 1

Just note, that if (for same column_a,column_b combination) two different column_c values have the same highest count, then it will be "random" which column_c value will be in the output.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Are you trying to do this?

SELECT t.*
FROM (SELECT COUNT(1) AS Num_Found, Column_A, Column_B, Column_C
      FROM Some_Table
      GROUP BY Column_A, Column_B, Column_C 
      ORDER BY COUNT(1) DESC
     ) t
WHERE rownum <= 2 ;

Upvotes: 0

Related Questions