Oracle SQL: Counting how often an attribute occurs for a given entry and choosing the attribute with the maximum number of occurs

Question

I have a table that has a number column and an attribute column like this:

1.
+-----+-----+
| num | att |
-------------
|  1  |  a  | 
|  1  |  b  |
|  1  |  a  |
|  2  |  a  |
|  2  |  b  |
|  2  |  b  |
+------------

I want to make the number unique, and the attribute to be whichever attribute occured most often for that number, like this (This is the end-product im interrested in) :

2.
+-----+-----+
| num | att |
-------------
|  1  |  a  | 
|  2  |  b  |
+------------

I have been working on this for a while and managed to write myself a query that looks up how many times an attribute occurs for a given number like this:

3.
+-----+-----+-----+
| num | att |count|
------------------+
|  1  |  a  |  1  |
|  1  |  b  |  2  |
|  2  |  a  |  1  |
|  2  |  b  |  2  |
+-----------------+

But I can't think of a way to only select those rows from the above table where the count is the highest (for each number of course).

So basically what I am asking is given table 3, how do I select only the rows with the highest count for each number (Of course an answer describing providing a way to get from table 1 to table 2 directly also works as an answer :) )

Answer 1

You can use aggregation and window functions:

select num, att
from (
    select num, att, row_number() over(partition by num order by count(*) desc, att) rn
    from mytable
    group by num, att
) t
where rn = 1

For each num, this brings the most frequent att; if there are ties, the smaller att is retained.