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.
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 elementorder by cnt
says how to define the order of elementsIn 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)