CODEWITHSUNDEEP
sqlsqlite
Sahil
Sahil

Reputation: 9478

SQL query for finding a guy with most friends?

I have a table of friends which has two columns, id1 which is the id of a student and id2 is the id of student id2.

Friend
column name: id1 -> id2

I have to find students with maximum friends.

I am getting data in this form:
person(id) --> number of friends in pp and ppp
by applying a group by on friend
and for that subquery I am selecting the student with highest count.

Here is my query:

select * from
(select kk.id1 as id3, count(*) as b from friend kk group by kk.id1) pp
where not exists 
(select * from
  (select kk1.id1 as id2, count(*) as b1 from friend kk1 group by kk1.id1) ppp
 where ppp.b1 > pp.b )

It is a straightforward query but I am getting incorrect results. Can anyone point out what I have done wrong?

Upvotes: 3

Views: 1436

Answers (2)

Josien
Josien

Reputation: 13877

Good question indeed. I've reformatted your query to make it slightly more readable and I've tested it both in SQL Server and in SQLite. In SQL Server the query seems to behave as expected, but in SQLite it does not. I can't find any explanation for this (yet), still working on it...

SELECT *
FROM   (SELECT F1.id1 as id3,
               count(*) as Count1
        FROM   Friend AS F1
        GROUP BY F1.id1) sub1
WHERE  NOT EXISTS ( SELECT *
                    FROM   (SELECT F2.id1 as id2,
                                   count(*) as Count2
                            FROM   Friend AS F2
                            GROUP BY F2.id1) sub2
                    WHERE  sub2.Count2 > sub1.Count1);

Upvotes: 1

John Woo
John Woo

Reputation: 263803

SELECT  A, COUNT(*) totalFriends
FROM
        (
            SELECT ID1 A, ID2 B FROM Friend
            UNION
            SELECT ID2 A, ID1 B FROM Friend
        ) s
GROUP   BY A
ORDER   BY totalFriends DESC
LIMIT 1

Upvotes: 0

Related Questions