Jeremy Foster Jeremy Foster - 1 year ago 68
SQL Question

SQL Query With Max Value from Child Table

Three pertinent tables: tracks (music tracks), users, and follows.

The follows table is a many to many relationship relating users (followers) to users (followees).

I'm looking for this as a final result:

<most popular followee>

The first two columns are simple and result from a relationship between tracks and users. The third is my problem. I can join with the follows table and get all of the followees that each user follows, but how to get only the most followee that has the highest number of follows.

Here are the tables with their pertinent columns:

tracks: id, user_id (fk to, song_title
users: id
follows: followee_id (fk to, follower_id (fk to

Here's some sample data:

1, 1, Some song title


2, 1
3, 1
4, 1
3, 4
4, 2
4, 3

1, 1, 4

For the desired result, the 3rd field is 4 because as you can see in the FOLLOWS table, user 4 has the most number of followers.

I and a few great minds around me are still scratching our heads.

Answer Source

So I threw this into Linqpad because I'm better with Linq.

    .Where(t => t.TrackId == 1)
    .Select(t => new { 
        TrackId = t.TrackId,
        UserId = t.UserId, 
        MostPopularFolloweeId = Followers
            .GroupBy(f => f.FolloweeId)
            .OrderByDescending(g => g.Count())

The resulting SQL query was the following (@p0 being the track id):

-- Region Parameters
DECLARE @p0 Int = 1
-- EndRegion
SELECT [t0].[TrackId], [t0].[UserId], (
    SELECT [t3].[FolloweeId]
    FROM (
        SELECT TOP (1) [t2].[FolloweeId]
        FROM (
            SELECT COUNT(*) AS [value], [t1].[FolloweeId]
            FROM [Followers] AS [t1]
            GROUP BY [t1].[FolloweeId]
            ) AS [t2]
        ORDER BY [t2].[value] DESC
        ) AS [t3]
    ) AS [MostPopularFolloweeId]
FROM [Tracks] AS [t0]
WHERE [t0].[TrackId] = @p0

That outputs the expected response, and should be a start to a cleaner query.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download