Oracle SQL get column value associated with max(count())

Question

I have the following query. What results is two coloums, oakid and max(count(rating)). What I want is to have two columns, but instead of actually having the max(count(rating)), I want the rating associated with the max(count(rating)). How can I modify my query to give me this?

select oakid, 
  (select max(count(rating)) 
     from climbs, routes 
     where climbs.routename = routes.name 
     and climbs.climberid = oakid group by routes.rating) 
  as skill
 from climbers;

For example, if I have ratings "hard, hard, easy, easy, easy, medium", the max(count(rating)) will show "3", since there are 3 "easy" ratings, but I want it to show "easy", not "3".

ninesided · Accepted Answer · 2012-03-05 05:23:58Z

2

It sounds as though you want the statistical mode (most frequently occurring) rating for each oakid or climberid. Oracle has a handy function to do this for us called STATS_MODE:

SELECT c.climberid AS oakid, STATS_MODE(r.rating) AS skill
FROM climbs c
INNER JOIN routes r ON (c.routename = r.name)
GROUP BY c.climberid;

edited Mar 5, 2012 at 5:23

answered Mar 5, 2012 at 5:15

ninesided

23.3k17 gold badges88 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

xdumaine Over a year ago

The way you rewrote the query doesn't work for my database, but the stats_mode was helpful. Thanks.

Vikram · Accepted Answer · 2012-03-05 05:11:19Z

0

try the following:

select oakid, 
  (select rating
     from climbs, routes 
     where climbs.routename = routes.name 
     and climbs.climberid = oakid 
         group by routes.rating 
         having max(count(rating)) = count(rating)) 
  as skill
 from climbers

answered Mar 5, 2012 at 5:11

Vikram

8,32335 silver badges47 bronze badges

Collectives™ on Stack Overflow

Oracle SQL get column value associated with max(count())

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related