CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
Pancake
Pancake

Reputation: 153

Oracle SQL select max "for each" row

I'm trying to find a way to select the rows with the highest value for each user in a table. Unless I'm misunderstanding, I can't simply group by each row

For example, say I have a query like this currently: 

select u.userid, p.region, count(u.userid) count
from userTable u join purchasesTable p on u.userid = p.userid
group by (u.userid, p.region)

and it gives a result like this currently: 

id          Region     COUNT
---------- ----------- ----------
1           East       1
2           West       1
3           North      1
1           North      2
2           East       3
3           West       4

I want to filter the results to show the region with the most purchases for each user (NOT the region or user with the most purchases overall). 

id          Region     COUNT
---------- ----------- ----------
1           North      2
2           East       3
3           West       4

I can't make the group by clause only contain the userid, because I want to include the region. But I don't want the region column to be factored in to the decision. I thought about wrapping this in another query which then takes the max values, but I haven't had any success with that approach. I also thought about using the distinct keyword, but that wouldn't solve this problem either.

If somebody could point me in the right direction, I would really appreciate it.

Upvotes: 0

Views: 934

Answers (2)

Piotr Rogowski
Piotr Rogowski

Reputation: 3880

try this, your query is inside from clausole:

select
id,region,max(count_)
from
(
    select u.userid, p.region, count(u.userid) count as count_
    from userTable u join purchasesTable p on u.userid = p.userid
    group by (u.userid, p.region)
) exe
group by id,region

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can do this with row_number():

select userid, region, cnt
from (select p.userid, p.region, count(*) as cnt,
             row_number() over (partition by p.userid order by count(*) desc) as seqnum
      from purchasesTable p 
      group by p.userid, p.region
     ) ur
where seqnum = 1;

Note: I removed the join from the query. For your query as stated, it is not necessary, because p.userid is the same as u.userid. Of course, if you wanted other information from the usersTable, then you would need the join to get it.

Upvotes: 0

Related Questions