CODEWITHSUNDEEP
sqlsql-servert-sql
Tom Steadman
Tom Steadman

Reputation: 11

How to show the TOP 5 rows of each result in a TOP 100 (T-SQL)

I'm trying to show the TOP 5 types of incident for each customer within the TOP 100 reporting customers, but I can't restrict the results to 5 incident types per customer. 

SELECT
a.[customer],
a.[incident type],
COUNT(a.[incident type) AS Subcount
FROM    [Table] a
JOIN 
(
SELECT TOP 100
[Contract],
[customer],
COUNT([customer]) AS CustomerCount
FROM
[table]
WHERE
...
GROUP BY [customer], [contract]
ORDER BY CustomerCount DESC ) b
ON      a.[customer] = b.[customer]
WHERE
Received >= DATEADD(MONTH,-3,GETDATE())
GROUP BY a.[incident type], a.[customer]
ORDER BY a.[customer], subcount DESC

The above is returning results in the format I want, but I've not been able to limit it to the top 5 "incident types".

Upvotes: 0

Views: 97

Answers (1)

HMan06
HMan06

Reputation: 785

Sample data would help but there are a few ways to do this. One way, which is easy and makes sense would be something like this,

SELECT [customer],
       [incident type],
       id
FROM [Table] a
         JOIN
     (
         SELECT [Contract],
                [customer],
                [incident type],
                row_number() over (partition by customer order by [incident type] desc) id
         FROM [table]
         WHERE Received >= DATEADD(MONTH, -3, GETDATE())
         GROUP BY [customer], [contract]
     ) a
WHERE id <= 5

Basically you are creating a partition by customer and ordering it by the incident type. Then you are saying, only show the top 5 incidents in the where statement (<=5), so anything over 5 for each customer will be thrown out. So an example you would see is something like this

customer     incident  id
john smith   yes       1
john smith   no        2
john smith   yes       3
john smith   yes       4
john smith   no        5
john smith   test      6
jane doe     newtest   1
jane doe     oldtest   2
jane doe     sometest  3

Play with this as you will.

Upvotes: 1

Related Questions