0

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

ahajib
  • 12,838
  • 29
  • 79
  • 120
  • What have you tried? What have you researched in trying to figure it out. You need to show that you have made at least an effort at doing it yourself. That is part of what SO requires. –  Aug 04 '15 at 20:38
  • @Anon316 just edited the question – ahajib Aug 04 '15 at 20:41
  • @Shomz if I do not mention in my question what I have tried so far it does not mean that I have done nothing. Also, I am here because I need help and am not asking my question in the most perfect way as I mentioned. If you have suggestions you can basically edit it instead of making fun of others !!! – ahajib Aug 04 '15 at 20:44
  • I wasn't making fun of you, but I've noticed you have a decent amount of reputation, so I assumed you know how to ask proper questions here. I can't edit your question, since **I** have no idea what **you** tried, and instead of letting people guess, it's much easier just to write it down... which you did in the end. Cheers! – Shomz Aug 04 '15 at 23:43
  • So the return data should be the most occurring `name` for each of the `id`? Previously, I thought it needed to return just the higher occurring name and id. – RisingSun Aug 05 '15 at 00:20
  • @khuderm Correct. Sorry if it wasn't clear enough before. – ahajib Aug 05 '15 at 00:26
  • That can be achieved but with subqueries. I'm sure there are ways to optimize so the subquery isn't being run on every single row. Here is a question that answers half of what you need. With some tweaks, Rahuls answer will give you the other half. I was gonna write the query but is getting that time of the day for me. http://stackoverflow.com/questions/23006042/mysql-select-multiple-all-max-values-in-the-same-column – RisingSun Aug 05 '15 at 00:37

3 Answers3

3

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

RisingSun
  • 1,693
  • 27
  • 45
biancamihai
  • 961
  • 6
  • 14
  • I assume "no" is equivalent for "A" in my table and "title" stands for "B" ? – ahajib Aug 04 '15 at 20:58
  • Yes, if you take a look at the demo http://sqlfiddle.com/#!9/dfa09/3 no is A and title is B. – biancamihai Aug 04 '15 at 21:00
  • This wont work if I add another row to the table. Edited the question again. But really cool demo. – ahajib Aug 04 '15 at 21:04
  • If instead of 1 I have 1,2,3, and so on and for each of them the same story, this query won't be able to handle that. As I described in my question, if there are two more rows where A is equal to 2 and B is 'Y' for both of them, the the query should return 2 rows where B for 1 is 'X' and B for 2 is 'Y'. – ahajib Aug 04 '15 at 21:09
  • tell us exactly what you need. otherwise you will get answers specific to what you are trying to do. hypothetical if this or that is not gonna be helpful. – RisingSun Aug 04 '15 at 22:43
0

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
Rahul
  • 76,197
  • 13
  • 71
  • 125
0

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
ahajib
  • 12,838
  • 29
  • 79
  • 120