CODEWITHSUNDEEP
sqlrsqlitegroup-bycount
adura826
adura826

Reputation: 113

Selecting the Highest result from each line

I have the following query which defines the highest watched shows:

SELECT 
    ti.num_views_per_telecast,
    t.episode_name,
    t.series_name
FROM (
    SELECT 
        ti.telecast_id,
        ti.network_id,
        count(*) as num_views_per_telecast
    FROM tunein AS ti
    INNER JOIN affiliates AS a ON ti.network_id = a.network_id
    WHERE ti.dvr_time_shift = 'L' and a.network_name = 'ABC'
    GROUP BY ti.telecast_id, ti.network_id
)ti
INNER JOIN telecast AS t ON t.telecast_id = ti.telecast_id
ORDER BY ti.num_views_per_telecast DESC

Currently, the results are coming back like this:

    num_views telecast          Episode Name            Series        

        100                         Football Game           Football       
        99                          Football Game           Football
        88                             Dancing 1              DWTS
        66                             Survivor               SVR
        55                             Survivor               SVR
        44                             Dancing 2              DWTS

        ...

I want it to only show the top Episode for each series, how would I display so it returns like this:

num_views telecast          Episode Name            Series        

100                         Football Game           Football       
88                             Dancing 1              DWTS
66                             Survivor               SVR
...

I tried the Max function but it only returned the top result for whatever I put it in front of

Upvotes: 0

Views: 41

Answers (1)

GMB
GMB

Reputation: 222632

One method uses ROW_NUMBER(). I think that it is simpler to flatten the joins in a subquery rather than pre-aggregating.

SELECT num_views_per_telecast, episode_name, series_name
FROM (
    SELECT 
        COUNT(*) as num_views_per_telecast,
        t.episode_name,
        t.series_name,
        ROW_NUMBER() OVER(PARTITION BY t.series_name ORDER BY COUNT(*) DESC) rn
    FROM tunein AS ti
    INNER JOIN affiliates AS a ON ti.network_id = a.network_id
    INNER JOIN telecast   AS t ON t.telecast_id = ti.telecast_id
    WHERE ti.dvr_time_shift = 'L' and a.network_name = 'ABC'
    GROUP BY ti.telecast_id, ti.network_id, t.episode_name, t.series_name
) t
WHERE rn = 1
ORDER BY num_views_per_telecast DESC

Upvotes: 1

Related Questions