SQL Counting values in each group and retrieving the maximum count for each value in a single column

Admin 1
Viewed 42

I am trying a SQL query (in Oracle) to get the number of occurrences of all combinations of values in columns V1 and V2. For this I used the query below.

SELECT V1, V2, COUNT(*) AS count 
FROM table1 
GROUP BY V1, V2

Output:

V1    V2    COUNT
-------------------
X     A         4
X     B         2
X     C         5
X     D         1
Y     A         8
Y     B         2
Y     C         8
Y     D         6

I now have a list of counts of all combinations of values in V1 and V2. I now want to select only the maximum counts for each value of V1 (if there are ties, show all). The Result should be as follows.

V1    V2    COUNT
-------------------
X     C         5
Y     A         8
Y     C         8
oracle sql
2 Answers

You can use the RANK window function as follows:

SELECT V1, V2, CNT from
(SELECT V1, V2, count(*) AS CNT,
rank() over (partition by V1 order by count(*) desc) as rn
FROM table1 GROUP BY V1, V2) where rn = 1
user_115656290

you can use dense_rank

select
    V1, 
    V2,
    count
from
(
    SELECT 
        V1, 
        V2, 
        count(*) as count,
        dense_rank() over (partition by V1 order by count(*) desc) as rnk
    FROM table1
    group by
       V1, V2  
) val
where rnk = 1
user_86913190
Related