2

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
4

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
GROUP BY id
HAVING COUNT(*) = 
      ( SELECT COUNT(*) AS cnt
        FROM Likes
        GROUP BY id
        ORDER BY cnt DESC
          LIMIT 1
      ) ;

Tested in SQL-Fiddle

| improve this answer | |
1

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)
SELECT *
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.