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:min(nation)
- min is meaningless in this case but you must keep it (doesn't work without)keep
- keeps only one result ofnation
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