Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

closed as not a real question by ypercube, StanleyJohns, dezso, Marian, Mark Storey-Smith May 27 '13 at 23:32

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

2 Answers

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

share|improve this answer
add comment

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)
share|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
add comment

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