Case Statement in Where Clause in SQL Server -

good day!

i have query using sql gives result set of sales per tenant. now, want final result set shows top 5 , bottom 5 in terms of sales (may flexible, sets 5 example)

i used rank function ranking per sales, , able desired output displaying top , bottom tenant based on sales.

here's part of code stored procedure

@rankedby int = 5  select *from (             select #temptable5.*, 'bottom' 'rankname' ,                    rank() on (partition business order sales ) rank                    #temptable5             ) rs rank <= @rankedby   union  select *from (             select #temptable5.*, 'bottom' 'rankname' ,                    rank() on (partition business order sales desc ) rank                    #temptable5             ) rs rank <= @rankedby   order  business, rankname desc ,rank      

and result set

tenant      business    sales   rankname    rank sample a1   food        1500    top            1 sample a2   food        1400    top            2 sample a3   food        1300    top            3 sample a4   food        1200    top            4 sample a5   food        1100    top            5 sample a6   food         100    bottom         1 sample a7   food         200    bottom         2 sample a8   food         300    bottom         3 sample a9   food         400    bottom         4 sample a10  food         500    bottom         5 sample b1   non food    2000    top            1 sample b2   non food    1800    top            2 sample b3   non food    1500    top            3 sample b3   non food    1500    bottom         1 sample b2   non food    1800    bottom         2 sample b1   non food    2000    bottom         3 

based on given information, achieved top , bottom based on number assigned in @rankedby parameter.

what want now, have condition where, if result set per business below or less parameter @rankedby, result should no longer include bottom part, sample correct result:

tenant      business       sales    rankname     rank     sample a1   food        1500    top            1     sample a2   food        1400    top            2     sample a3   food        1300    top            3     sample a4   food        1200    top            4     sample a5   food        1100    top            5     sample a6   food         100    bottom         1     sample a7   food         200    bottom         2     sample a8   food         300    bottom         3     sample a9   food         400    bottom         4     sample a10  food         500    bottom         5     sample b1   non food    2000    top            1     sample b2   non food    1800    top            2     sample b3   non food    1500    top            3 

non food has 3 records only, less parameter assigned in @rankedby 5.

on existing code, planned have count per business , put condition in clause, found inefficient , ineffective.

if among experts give more efficient coding or correct initial codes.

thanks in advance

i create temporary table result of full query, 2 simple queries order sales limit 5 , order sales desc limit 5.

the benefit can other refinements on temporary table if need, computing mean, average,...