CODEWITHSUNDEEP
sql-server-2008
Andha
Andha

Reputation: 917

SQL SELECT TOP N per ID, per Time, per Group

I'm really having a hard time to write the stored procedure. Here is the case.

I'm joining several tables in a stored procedure and store it in a temporary table. Below is the illustration of the result given by the stored procedure :

CLIENT        Request     Month   Total

Client A      A           1       1
Client A      B           1       3
Client A      C           1       9
Client A      D           1       4 
Client A      E           1       6

Client A      A           2       6
Client A      B           2       9
Client A      C           2       1
Client A      D           2       3 
Client A      E           2       2

Client B      A           1       3
Client B      B           1       7
Client B      C           1       9
Client B      D           1       8 
Client B      E           1       4

Client B      A           2       5
Client B      B           2       8
Client B      C           2       1
Client B      D           2       1 
Client B      E           2       3

My question is, how can I get the TOP N (eg : top 3) for each clients per month based on the highest total request.

I'm going to use the stored procudere for a chart in Reporting Service, i'm also having a hard time using the filter function in the chart, so i think there's any possibilities to filter it at the first place from the sql query.

I hope me and the illustration above explained the problem clearly. I really appreciate every responses from you. Thanks so much before

Upvotes: 2

Views: 1577

Answers (3)

Pranay Rana
Pranay Rana

Reputation: 176934

Make use of the GROUPBY clause to find out the counts made by each client, and then use HAVING to filter out the data of created GROUP BY.

Upvotes: 0

Yogesh Bhadauirya
Yogesh Bhadauirya

Reputation: 1235

DECLARE @TABLE Table
(
    CLIENT varchar(100),Request varchar(10),[Month] int,Total int
)

Insert into @Table

Select 'Client A','A',1,1 UNION ALL
Select 'Client A','B',1,3 UNION ALL
Select 'Client A','C',1,9 UNION ALL
Select 'Client A','D',1,4 UNION ALL
Select 'Client A','E',1,6 UNION ALL
Select 'Client A','A',2,6 UNION ALL
Select 'Client A','B',2,9 UNION ALL
Select 'Client A','C',2,1 UNION ALL
Select 'Client A','D',2,3 UNION ALL
Select 'Client A','E',2,2 UNION ALL
Select 'Client B','A',1,3 UNION ALL
Select 'Client B','B',1,7 UNION ALL
Select 'Client B','C',1,9 UNION ALL
Select 'Client B','D',1,8 UNION ALL
Select 'Client B','E',1,4 UNION ALL
Select 'Client B','A',2,5 UNION ALL
Select 'Client B','B',2,8 UNION ALL
Select 'Client B','C',2,1 UNION ALL
Select 'Client B','D',2,1 UNION ALL
Select 'Client B','E',2,3

Declare @Top int
Set @Top=2

;with CTE AS
(
    SELECT CLIENT,Request,[Month],Total,
    --month wise top record based on total request 
    ROW_NUMBER()Over(Partition by CLIENT,[Month] Order by  Total Desc) AS RN
    FROM @Table
)
Select *
From CTE
where rn<=@Top
Order by CLIENT

Upvotes: 3

Ovidiu Pacurar
Ovidiu Pacurar

Reputation: 8209

Use row_number with partition by CLIENT and order by TOTAL DESC

Upvotes: 0

Related Questions