Why is Rank() OVER PARTITION BY returning too many results

Question

I want the results of my query to be the top 3 newest, distinct Campaign Names for each Campaign Type.

My query at the moment is:

DECLARE @currentRecord varchar(160);
SET @currentRecord = '316827D2-B522-E811-816A-0050569FE3BD';

SELECT DISTINCT
       rs.CampaignName,
       rs.CampaignType,
       rs.receivedon,
       rs.Rank
FROM 
    (SELECT 
         fs_retentioncontact,
         receivedon,
         regardingobjectidname AS CampaignName,
         fs_campaignresponsetypename AS CampaignType,
         RANK() OVER (PARTITION BY fs_campaignresponsetypename, regardingobjectidname
                      ORDER BY receivedon DESC) AS Rank
     FROM 
         dbo.FilteredCampaignResponse) rs
INNER JOIN 
    dbo.FilteredContact ON rs.fs_retentioncontact = dbo.FilteredContact.contactid
WHERE 
    (dbo.FilteredContact.parentcustomerid IN (@currentRecord))
    AND Rank <= 3
ORDER BY 
    CampaignType, receivedon DESC;

There may be multiple results for each campaign name as well as campaign response because they are linked to individual contacts but I only want to see the 3 latest unique campaigns for each campaign type.

My query is not partitioning by each individual campaign response type (there are 6 different ones) as I was expecting. If I remove the regardingobjectidname from the PARTITION BY I only get a single row in the results when I should be getting 18 rows. This particular company has over 700 campaign responses across the 6 campaign types.

My query is returning 102 rows so it seems to be removing duplicates on campaign name which is part of what I need but not the whole story.

I have read quite a few posts regarding rank() on here e.g. how-to-use-rank-in-sql-server [ using-sql-rank-for-overall-rank-and-rank-within-a-group]2 but I am not able to work out what I am doing wrong from their examples. Could it be the positioning of the 'receivedon' in the ORDER BY? or something else?

FYI, SQL Server 2008 has been out of support for well over a year now, and you should really be looking into upgrade paths ASAP. Also why use `IN (@currentRecord)`? `@currentRecord` is a scalar value so you might as well use the `=` operator there. — Thom A, Oct 28 '20 at 16:11
It's almost always an error to repeat a column/expression in the `ORDER BY` that appears in the `PARTITION BY` - by **definition** you know that all rows within each partition have the *same value* for that column/expression. It thus provides no assistance in assigning an ordering to those rows. — Damien_The_Unbeliever, Oct 28 '20 at 16:46
@Larnu - my query is not about the IN(@currentRecord) as that works perfectly well for the reason that it is there. It is part of a much more complicated query and does a good job that the equals would not do. — Caroline Allen, Oct 29 '20 at 08:14
Thank you @Damien_The_Unbeliever for offering help. I have adjusted the query (and in the post above) but I am still not getting what I need. Is Rank() the correct expression to be using to get the top 3 of each group? — Caroline Allen, Oct 29 '20 at 08:18

score 0 · Answer 1 · answered Oct 29 '20 at 09:25

I have finally worked out from reading a post on another site how to get the top 3 of each group. I shall post my answer in case it helps anyone else. I had to use ROW_NUMBER() OVER (PARTITION BY instead of RANK() OVER (PARTITION BY and I also moved the INNER JOIN and WHERE clause (to filter for the correct company) from the outer query to the inner query.

DECLARE @currentRecord varchar(160)
SET @currentRecord='316827D2-B522-E811-816A-0050569FE3BD'
SELECT distinct rs.CampaignName
                ,rs.CampaignType
                , rs.receivedon
                ,RowNum
FROM(
        SELECT fs_retentioncontact
        , receivedon
        , regardingobjectidname AS CampaignName
        ,fs_campaignresponsetypename as CampaignType
        ,ROW_NUMBER() OVER (PARTITION BY fs_campaignresponsetypename ORDER BY fs_campaignresponsetypename, receivedon DESC) AS RowNum
        FROM FilteredCampaignResponse
        INNER JOIN dbo.FilteredContact ON fs_retentioncontact = dbo.FilteredContact.contactid
        WHERE(dbo.FilteredContact.parentcustomerid IN (@currentRecord)))rs


WHERE RowNum <=3
ORDER BY CampaignType,receivedon DESC;

Why is Rank() OVER PARTITION BY returning too many results

1 Answers1