Table Schema for the Likes table:

id id1
1  2
1  3
2  1

I count the total number of Likes for each student with this:

select id,count(*)
from friends
group by id

I have to find the students with the maximum number of Likes, so I use this query as a temp table:

select * from (
  select f1.id,count(*) as count1
  from Likes f1
  group by id
) temp
where not exists (
  select f2.id,count(asterick) as count2
  from Likes f2 group by f2.id having count2 > temp.count1

It returns all of the records. Can anyone point out what I am doing wrong in this query?

  • Have you solved this problem? – ypercubeᵀᴹ May 26 '13 at 9:22
  • Can you provide an SSCCE either here or via SQL Fiddle that reproduces the problem? – Nick Chammas May 27 '13 at 5:58
  • @NickChammas The fiddle in my answer reproduces the problem. Not sure what version of SQLite it uses though. Perhaps it's valid for a bug report. – ypercubeᵀᴹ May 27 '13 at 10:45
  • @ypercube Nope, I gave up on it after I posted this question here. – Dude May 27 '13 at 13:44

Your query is correct and works fine in other DBMS (SQL-Server, Postgres, Oracle, MySQL). In SQLite, it appears there is a bug, possibly due to the correlated subquery and/or the grouping in both the main and the sub query. Here is another way to write the query:

SELECT id, COUNT(*) AS count1
FROM Likes
      ( SELECT COUNT(*) AS cnt
        FROM Likes
        GROUP BY id
        ORDER BY cnt DESC
          LIMIT 1
      ) ;

Tested in SQL-Fiddle

| improve this answer | |

If I understand your requirements correctly this is a simplified query to get your results, although when I tested yours it worked fine as well. The simplified one may point out a way to get the correct results in your real query with any luck.

WITH Totals AS (SELECT id, COUNT(1) AS count1 
                FROM Likes GROUP BY id)
FROM Totals f1
WHERE count1 = (SELECT MAX(count1) FROM Totals)
| improve this answer | |
  • 1
    The query seems correct but have you tested it in SQLite? I don't think sqlite has CTEs. – ypercubeᵀᴹ Feb 25 '13 at 9:12

Not the answer you're looking for? Browse other questions tagged or ask your own question.