CODEWITHSUNDEEP
sqloracle-databaseoracle12c
Rahul Chandak
Rahul Chandak

Reputation: 33

SQL apply MAX to left join table having non null rows

table name: country

id country_name
1  USA
2  GERMANY
3  RUSSIA

table name: user


id user_name  points country_id
1  user1      20     1
2  user2      10     2
3  user3      11     2

Result should be country-user with maximum points and only country if no user available(3rd record),like below

country_name user_name points
USA          user1     20
GERMANY      user3     11
RUSSIA       (null)    (null)

Currently I am using below query but it is sometime taking too much time, like when i have 100000 records.

SELECT c.country_name,u.user_name,u.points FROM country c
LEFT JOIN user u on u.country_id = c.id 
WHERE (u.points = (SELECT MAX(points) AS points FROM user WHERE user.id = u.id) OR u.points IS NULL)

So, is there any other way to do it more efficiently, time-wise.

Thanks already!

Upvotes: 1

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You can use ROW_NUMBER():

SELECT c.country_name, u.user_name, u.points
FROM country c LEFT JOIN
     (SELECT u.*,
             ROW_NUMBER() OVER (PARTITION BY u.country_id ORDER BY u.points DESC) as seqnum
      FROM user u
      WHERE u.points IS NOT NULL
     ) u
     ON u.country_id = c.id AND u.seqnum = 1;

Note: This returns one user per country, even if there are ties for the top one. If you want all of them, use RANK() instead of ROW_NUMBER().

Upvotes: 1

Related Questions