Oracle SQL returning rows same max value

Question

Here is my table

 ------------------
|__fruit__|_color_|
| banana  | yellow|
| lemon   | yellow|
| apple   |  red  |
| cherry  |  red  |
| lime    | green |

The output from the query should be:

yellow, banana
yellow, lemon
red, apple
red, cherry

I'm wanting to return the fruit with the maximum amount of the count of color. Since both the color red and the color yellow have the same maximum amount of color, I havent been able to get them both. I have tried using distinct keywords, as well as trying different aggregate functions using count but have only been able to return one color.

Kindly share what you have tried so far.

EvilTeach
– EvilTeach

2015-09-19 22:57:12 +00:00
Commented Sep 19, 2015 at 22:57 — EvilTeach
– EvilTeach, Commented Sep 19, 2015 at 22:57

Vamsi Prabhala · Accepted Answer · 2015-09-19 22:41:47Z

1

You have to group by color and join it to the original table to get the desired result.

Fiddle

with cnts as (select color,count(*) as cnt
from tablename 
group by color)
, maxcnt as (select max(cnt) as mxcnt from cnts)
select t.color,t.fruit
from cnts c join maxcnt m on c.cnt = m.mxcnt
join tablename t on t.color = c.color

edited Sep 19, 2015 at 22:41

answered Sep 19, 2015 at 22:26

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

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

2 Comments

REAL O G Over a year ago

The edited version only returns red and not yellow, and is much more confusing for a beginner.

Vamsi Prabhala Over a year ago

the earlier version..was explicitly returning all rows where count was > 1. However, with the modified version, you get only rows for colors where there is a maximum count, which was your question. Also, i added more rows in the fiddle for your understanding

Collectives™ on Stack Overflow

Oracle SQL returning rows same max value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related