Oracle Sql gets the value that appears most often for each other value of another column

Question

So let's say that I have this table

language | offer
chinese  |   1
chinese  |   1
english  |   1
spanish  |   2
spanish  |   2
italian  |   2
french   |   3

and I want the language that appears most often for every other sentence, like this

language | offer
chinese  |   1
spanish  |   2
french   |   3

How to do it in sql? oracle?

Answer 1

This is one way to do this using common table expressions.

SQL Fiddle

In the first article, you calculate the counts, grouped by sentence and language. In the following quote, use rankor row_numberto designate the 1sentence with the highest number of languages. Finally, choose from ranks of 1st rank.

with counts as(
select offer, language, count(*) cnt
from tablename
group by offer, language)
,ranking as
(select rank() over(partition by offer order by cnt desc) rnk
, c.*
from counts c)
select language, offer
from ranking 
where rnk = 1

An alternative approach without window functions:

with counts as (
select offer, language, count(*) cnt
from tablename
group by offer, language)
,maxcount as (select offer, max(cnt) mxcnt from counts group by offer)
select c.language, m.offer
from counts c
join maxcount m on m.offer = c.offer and m.mxcnt = c.cnt

Answer 2

row_number oracle .

 SELECT LANGUAGE
        ,offer
    FROM (
        SELECT LANGUAGE
            ,offer
            ,row_number() OVER (
                PARTITION BY offer ORDER BY count(offer) DESC
                ) AS rno
        FROM table1
        GROUP BY LANGUAGE
            ,offer
        )
    WHERE rno = 1

Answer 3

:

select offer, language
from tablename t
group by offer, language
having not exists
(
  select 1
  from tablename
  where offer = t.offer
  group by language
  having count(language) > count(t.language)
)

In the main query, you group each language into a sentence and check in the subquery that no other language group from the same sentence contains more occurrences.