how can i get max value for each country

Question

i'm using oracle sql developer. i have two tables

transactions:

╔════╤═══════╗
║ id │ value ║
╠════╪═══════╣
║ 1  │ 10    ║
╟────┼───────╢
║ 1  │ 20    ║
╟────┼───────╢
║ 2  │ 30    ║
╟────┼───────╢
║ 3  │ 40    ║
╚════╧═══════╝

and users:

╔════╤═════════╤═════╗
║ id │ country │ sex ║
╠════╪═════════╪═════╣
║ 1  │ Germany │ m   ║
╟────┼─────────┼─────╢
║ 2  │ Germany │ f   ║
╟────┼─────────┼─────╢
║ 3  │ France  │ m   ║
╚════╧═════════╧═════╝

i want to get max value for each country like this

╔════╤═════════╤═════╤══════════╗
║ id │ country │ sex │ maxvalue ║
╠════╪═════════╪═════╪══════════╣
║ 2  │ Germany │ f   │ 30       ║
╟────┼─────────┼─────┼──────────╢
║ 3  │ France  │ m   │ 40       ║
╚════╧═════════╧═════╧══════════╝

i know how to get max value for each user and join tables

SELECT u.*, max
FROM users u
LEFT JOIN
(SELECT id, max(value) as max
FROM transactions 
group by ID) t
on u.id = t.id;

what should i change to get a max value for each country?

what will be the expected output if the values were 20, 30 for id = 1 in transactions table? Will it replace France to Germany? — Slkrasnodar, Apr 28 '18 at 14:24
Id 1 value is also 30 (10 + 20). So, id 1 and id 2 max values are same. So, what now? why do we choose 2 or 1? — DxTx, Apr 28 '18 at 15:19

score 2 · Answer 1 · answered Apr 28 '18 at 15:20

This might work:

SELECT id, country, sex, value AS maxvalue FROM (
    SELECT u.id, u.country, u.sex, COALESCE(t.value, 0) AS value
         , ROW_NUMBER() OVER ( PARTITION BY u.country ORDER BY t.value DESC NULLS LAST ) AS rn
      FROM users u LEFT JOIN transactions t
        ON u.id = t.id
) WHERE rn = 1;

This will get all the values for each country and rank them, then retrieve only the one with the top rank. If you want to get ties as well then use RANK() instead of ROW_NUMBER():

SELECT id, country, sex, value AS maxvalue FROM (
    SELECT u.id, u.country, u.sex, COALESCE(t.value, 0) AS value
         , RANK() OVER ( PARTITION BY u.country ORDER BY t.value DESC NULLS LAST ) AS rn
      FROM users u LEFT JOIN transactions t
        ON u.id = t.id
) WHERE rn = 1;

Hope this helps.

score 1 · Answer 2 · answered Apr 28 '18 at 14:15

1

The traditional method would not use a subquery, but simply join and group by:

SELECT u.country, MAX(t.value)
FROM users u LEFT JOIN
     transactions t
     ON u.id = t.id
GROUP BY u.country;

answered Apr 28 '18 at 14:15

Gordon Linoff

1,242,037
58
646
786

May be OP wants to group also by `sex`. The sample expected data don't match with text explanation. – dani herrera Apr 28 '18 at 14:16
your code works but only when i want to show country and max value, when i want to show country, max value and sex i got an error - "Error report - SQL Error: ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression"" – riloth Apr 28 '18 at 14:24
hi @gordonLinoff, I told to you ;) – dani herrera Apr 28 '18 at 14:59
i dont want to group by sex but i want to show it in a table – riloth Apr 28 '18 at 15:02
yes, funny. I'm expecting the end of this thread. I go for popcorn. – dani herrera Apr 28 '18 at 15:04

score 1 · Answer 3 · answered Apr 28 '18 at 14:24

1

This might get the required result

SELECT u.id, u.country, u.sex, MAX(t.value)
FROM users u 
LEFT JOIN transactions t ON u.id = t.id
GROUP BY u.id, u.country, u.sex;

answered Apr 28 '18 at 14:24

Inaam ur Rehman

470
1
6
23

your code its not working, it doesnt shows maxvalue for each country – riloth Apr 28 '18 at 14:31
my query showing max value for **country** and **sex**, if you want only country wise value than remove u.id and u.sex from both the select and group by clause – Inaam ur Rehman Apr 28 '18 at 14:42
Error report - SQL Error: ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" *Cause: *Action: – riloth Apr 28 '18 at 14:47
This error occurs when you don't have all the fields of selected clause in group by clause while using an aggregate function. see [this](https://stackoverflow.com/questions/13962772/not-a-group-by-expression-error) – Inaam ur Rehman Apr 28 '18 at 15:03
my real table is much bigger and i have there more than 2 sexes – riloth Apr 28 '18 at 15:07
so what? more than two sexes and table size (rows in table) does not matter. – Inaam ur Rehman Apr 28 '18 at 15:13

score 1 · Answer 4 · answered Apr 28 '18 at 15:01

You have to first find the max per country and the do another join for the sex, like this:

SELECT u.*, t.value
FROM transactions t
INNER JOIN users u ON t.id = u.id
INNER JOIN
(SELECT us.country, MAX(tr.value) AS max_value
 FROM transactions tr
 INNER JOIN users us ON tr.id = us.id
 GROUP BY us.country) sub ON t.value = sub.value AND u.country = sub.country

DxTx · Accepted Answer · 2018-04-28T15:23:17.260

1

Try this.

select tmp.country, tmp.maxvalue, tmp.id, users.sex from (
select users.country as country, max(transactions.value) as maxvalue, max(users.id) as id 
from users
inner join transactions on users.id = transactions.id
group by users.country) tmp
inner join users on tmp.id = users.id;

Demo: http://www.sqlfiddle.com/#!4/5f476/27/0

edited Apr 28 '18 at 15:23

answered Apr 28 '18 at 15:14

DxTx

3,049
3
23
34

score 1 · Answer 6 · answered Apr 29 '18 at 16:21

1

select * from (
SELECT t.ID, u.COUNTRY, T.VALUE, 
row_number() over(partition by u.country order by t.value desc) rn
FROM USERS u , transactions t
where u.id = t.id)
where rn = 1

answered Apr 29 '18 at 16:21

Leo

868
1
13
32

how can i get max value for each country

6 Answers6