0

I have table people containing people, their city and their money balance:

id    city_id    money
1     1          25
2     1          13
3     2          97
4     2          102
5     2          37

Now, I would like to select richest person from each city. How can I do that using Oracle SQL? Desired result is:

id    city_id    money
1     1          25
4     2          102

Something like that would be useful:

SELECT * as tmp FROM people GROUP BY city_id HAVING money = MAX(money)
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Michal
  • 1,755
  • 3
  • 21
  • 53

3 Answers3

1

You should be thinking "filtering", not "aggregation", because you want the entire row. You can use a subquery:

select p.*
from people p
where p.money = (select max(p2.money) from people p2 where p2.city_id = p.city_id);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You can use RANK() as its flexible as you can get richest or top N richest

SELECT
 id, city_id, money
   FROM (
    SELECT 
    p.* ,RANK() OVER (PARTITION BY city_id ORDER BY money DESC ) as rank_per_city
    FROM 
    people p )
WHERE
rank_per_city = 1
Yefet
  • 2,010
  • 1
  • 10
  • 19
0

You can use DENSE_RANK() analytic function through grouping by city_id(by using partition by clause) and descendingly ordering by money within the subquery to pick the returned values equal to 1 within the main query in order to determine the richest person including ties(the people having the same amount of money in each city) such as

SELECT id, city_id, money
  FROM( SELECT p.*,
               DENSE_RANK() OVER ( PARTITION BY city_id ORDER BY money DESC ) AS dr
          FROM people p )  
 WHERE dr = 1
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • please check out again whether the same @Yefet and compare the results through [demo](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=b565e9db506f31c27474bf4e0c88c7fa) – Barbaros Özhan May 08 '21 at 12:59