Filter rows from oracle sql table using max value of a column

Question

I am learning oracle SQL, and I have a table like the following:

+--------+--------+------------------------+
| name   | nation | count                  |
+--------+--------+------------------------+
| Ruben  | UK     | 2                      |
| Ruben  | EEUU   | 16                     |
| Cesar  | UK     | 21                     |
| Cesar  | EEUU   | 12                     |
| Cesar  | FRANCE | 4                      |
| John   | FRANCE | 3                      |
| John   | UK     | 7                      |
| ....   | ..     | ..                     |
+--------+--------+------------------------+

The table above represents an inline view I created with a query. As you can see, the table groups by name and by nation and do some count. I want to filter the table using the count column to have something like this:

+--------+--------+
| name   | nation |
+--------+--------+
| Ruben  | EEUU   |
| Cesar  | UK     |
| John   | UK     |
| ....   | ..     |
+--------+--------+

As you can see, for each name I want to choose the nation based on the count.

score 4 · Accepted Answer · answered Mar 05 '14 at 16:43

Use keep analytic keyword:

select name, min(nation) keep (dense_rank last order by cnt)
from (select name, nation, count(*) as cnt
      from /* your data source */
      group by name, nation)
group by name

min(nation) - min is meaningless in this case but you must keep it (doesn't work without)
keep - keeps only one result of nation
dense_rank last says to pick up the last element
order by cnt says how to define the order of elements

In the end it will make for every name the nation with the biggest count. The same result can be achieved with

select name, min(nation) keep (dense_rank first order by cnt desc)

score 3 · Answer 2 · answered Mar 05 '14 at 15:01

You can do this with another level of inner query. Something like:

select name, nation
from (
  select name, nation, cnt,
    row_number() over (partition by name, nation order by cnt desc) as rn
  from (
    select name, nation, count(*) as cnt
    from <your table>
  )
)
where rn = 1;

The row_number() analytic adds a pseudo column to the innermost query that ranks the row with the highest count as 1, for each name/nation; the outermost query then only looks at those ranked first.

You need to decide what to do about ties - if two nation/name rows have the same count, you could show both by using rank() instead of row_number(); or you could decide which one to choose by adding another column to the order by clause. At the moment, if there are two the same, it will only show you one of them - and which it shows is rather arbitrary.

Filter rows from oracle sql table using max value of a column

2 Answers2