CODEWITHSUNDEEP
sqlsql-server
shanlodh
shanlodh

Reputation: 1045

SQL Server: Select top N records in each category: Count-Rank-Select in one script

Existing questions already have count of underlying per category and then consider how to select top 10 per category but would it be possible to do it end-to-end in one script i.e. count per category, rank by category and then select top 10 per category? I've done the first 2, now wondering how to incorporate the third i.e. selecting top N results within the same script:

;with cte as 
(
    select 
        custID, 
        day(tradedate) as TradeDate, 
        sum(tradecount) as TradeCountSum 
    from 
        tradetable
    where 
        tradedate >= '2018-6-17' 
    and 
        tradedate <= '2019-6-17'  
    group by 
        custID, 
        day(tradedate) 
)
select 
    custID, 
    tradedate, 
    TradeCountSum * 100 / sum(TradeCountSum) over (partition by custID) as TradeCountPercent 
    from cte 
    order by custID asc, TradeCountPercent desc

Thanks

Upvotes: 0

Views: 206

Answers (1)

Dwight Reynoldson
Dwight Reynoldson

Reputation: 960

One way of doing it would be to use ntile with your result set and then wrapping that in to a derived query and using the ntile value in a where clause.

In the below example I've split your result set in to 10% chunks ordered by tradecountpercent descending and then queried the top one of those.

I've also removed the ";" from before the with clause as this is not necessary, as long as the statement preceding the with clause is terminated with a semicolon. 

declare @tradetable table (
    custid int,
    tradedate date,
    tradecount int
);

insert @tradetable (custid, tradedate, tradecount)
values
(1, '2018-06-17', 3),
(1, '2018-06-24', 1),
(1, '2018-07-02', 12),
(1, '2018-07-15', 4),
(1, '2018-07-21', 8),
(1, '2018-07-30', 56),
(1, '2018-08-29', 7),
(1, '2018-09-12', 2),
(1, '2018-10-17', 8),
(2, '2018-06-17', 3),
(2, '2018-06-24', 1),
(2, '2018-07-02', 12),
(2, '2018-07-15', 4),
(3, '2018-07-21', 8),
(3, '2018-07-30', 56),
(3, '2018-08-29', 7),
(3, '2018-09-12', 2),
(4, '2018-10-17', 8);


with cte as 
(
    select 
        custID, 
        day(tradedate) as TradeDate, 
        sum(tradecount) as TradeCountSum 
    from 
        @tradetable
    where 
        tradedate >= '2018-6-17' 
    and 
        tradedate <= '2019-6-17'  
    group by 
        custID, 
        day(tradedate) 
)

select * from (

select ntile(10)over(order by tradecountpercent desc) percents, * from
(
select 
    custID, 
    tradedate, 
    TradeCountSum * 100 / sum(TradeCountSum) over (partition by custID) as TradeCountPercent 
    from cte )x ) y where percents = 1
    order by custID asc, TradeCountPercent desc

Upvotes: 1

Related Questions