Query to find Maximum friends not giving correct resuts

Question

Table Schema

Likes table
id,id1
1,2
1,3
2,1

I have count of total number of Likes for each student through this

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

Now I have to find students with maximum Likes, so I use this query as 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.

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)