CODEWITHSUNDEEP
sqlt-sql
MathiasH
MathiasH

Reputation: 115

Complex TSQL query (Top n and Group by combined)

I know TSQL pretty well but I have definately hit a wall here.

I am making some statistics on some Agents and I use group by to structure the data like this:

    select 
    AgentID, 
    [No 14] as answer_option, 
    COUNT(*) as amount_of_answers, 
    CASE 
        WHEN [No 14] = 1 then CONVERT(int, COUNT(*), 1) * 5 
        WHEN [No 14] = 2 then CONVERT(int, COUNT(*), 1) * 4 
        WHEN [No 14] = 3 then CONVERT(int, COUNT(*), 1) * 3 
        WHEN [No 14] = 4 then CONVERT(int, COUNT(*), 1) * 2 
        WHEN [No 14] = 5 then CONVERT(int, COUNT(*), 1) * 1 
        END as combined_weight
from #temptable
where 
[No 14] <> '-'
and AgentID <> '-1'
group by AgentID, [No 14]
order by AgentID, svar

Now this was all working great until I was told the data should only be based on the last 20 incomming rows per agent.

The last 20 rows for an agent can be found like this:

select TOP 20 * from #temptable where AgentID = X order by changedDate desc

How do I combine these two queries so I only get the top 20 rows per agent based on ChangedDate desc into my group by query?

If there is only 15 rows, it should just be based on these.

Upvotes: 1

Views: 715

Answers (1)

Andomar
Andomar

Reputation: 238196

You could use row_number() to filter out the last 20 rows per agent. Change:

from #temptable

to:

from    (
        select  row_number() over (partition by AgentID 
                                   order by ChangedDate desc) as rn
        ,       *
        from    #temptable
        ) as SubQueryAlias
where   rn <= 20
        and ...

Upvotes: 3

Related Questions