sqloracle-databasemaxinline-view

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


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.


Solution

  • 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
    
    1. min(nation) - min is meaningless in this case but you must keep it (doesn't work without)
    2. keep - keeps only one result of nation
    3. dense_rank last says to pick up the last element
    4. 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)