CODEWITHSUNDEEP
sql-servert-sql
Ivo Oostwegel
Ivo Oostwegel

Reputation: 448

Count top 5 persons that were most together

I have a check-in table that consists of the flowing columns: 

PK CheckInID int
PersonID int
CheckInDate smalldatetime

I'm trying to create a query that gives me a top 3 of persons who most frequently were checked-in together for a specific person.

For example:

personID 1 was 
18 times together with personID 3
13 times together with personID 9
11 times together with personID 4

Implementing this in C# is not really a problem for me but I want to create a stored procedure and TSQL is not really my strong side.

Upvotes: 1

Views: 75

Answers (2)

Stéphane CLEMENT
Stéphane CLEMENT

Reputation: 372

A faster way (no subquery and no "IN" statement) is :

SELECT TOP 3 T2.PersonId
   , SUM(1) AS NB_TIME_CHECKED_IN_WITH_XXX
FROM your_table AS T1
   INNER JOIN your_table AS T2 ON (T1.[PK CheckInID]=T2.[PK CheckInID] AND T2.PersonId <> XXX)
WHERE T1.PersonId = XXX
GROUP BY PersonId
ORDER BY NB_TIME_CHECKED_IN_WITH_XXX DESC;

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

Assuming that date is designator:

SELECT TOP 3 PersonId, COUNT(*) cnt
FROM your_table
WHERE CheckInDate IN (SELECT CheckInDate 
                      FROM your_table     
                      WHERE PersonId = ?)  
  AND PersonId <> ?  -- do not count the same person
GROUP BY PersonId
ORDER BY cnt DESC;

Upvotes: 1

Related Questions