CODEWITHSUNDEEP
mysqlmax
ahajib
ahajib

Reputation: 13510

select max() from result of count for distinct ids in mysql

I have a table with the following structure:

id    name
1     X
1     X
1     Y
2     A
2     A
2     B

Basically what I am trying to do is to write a query that returns X for 1 because X has repeated more than Y (2 times) and returns A for 2. So if a value occurs more than the other one my query should return that. Sorry if the title is confusing but I could not find a better explanation. This is what I have tried so far:

SELECT MAX(counted) FROM(
    SELECT COUNT(B) AS counted
    FROM table
    GROUP BY A
) AS counts;

The problem is that my query should return the actual value other than the count of it.

Thanks

Upvotes: 0

Views: 246

Answers (3)

ahajib
ahajib

Reputation: 13510

This is how I finally handled my problem. Not the most efficient way but get the job done:

select A,B from 
(select A,B, max(cnt) from 
(select A ,B ,count(B) as cnt 
    from myTable 
    group by A,B 
    order by cnt desc
) as x group by A
) as xx

Upvotes: 0

biancamihai
biancamihai

Reputation: 981

This should work:

SELECT count(B) as occurrence, A, B
FROM table
GROUP BY B 
ORDER BY occurrence DESC
LIMIT 1;

Please check: http://sqlfiddle.com/#!9/dfa09/3

Upvotes: 3

Rahul
Rahul

Reputation: 77876

You can try like this using a GROUP BY clause. See a Demo Here 

select *, max(occurence) as Maximum_Occurence from
(
select B, count(B) as occurence
from table1
group by B
) xxx

Upvotes: 0

Related Questions