Case Statement in Where Clause in SQL Server

Question

Good day!

I have a query using SQL which gives a result set of sales per tenant. Now, I want to get a final result set that shows the top 5 and bottom 5 in terms of sales (may be flexible, but sets 5 as an example)

I used rank function to get the ranking per sales, and was able to get almost the desired output by displaying the top and bottom tenant based on sales.

Here's the part of the code from stored procedure

@RankedBy int = 5

SELECT *FROM (
            SELECT #TEMPTABLE5.*, 'BOTTOM' AS 'RankName' ,
                   Rank() over (Partition BY Business ORDER BY Sales ) AS Rank
                   FROM #TEMPTABLE5
            ) rs WHERE Rank <= @RankedBy 

UNION ALL

SELECT *FROM (
            SELECT #TEMPTABLE5.*, 'BOTTOM' AS 'RankName' ,
                   Rank() over (Partition BY Business ORDER BY Sales DESC ) AS Rank
                   FROM #TEMPTABLE5
            ) rs WHERE Rank <= @RankedBy 

order by  Business, RankName DESC ,Rank

and this is the 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 the given information, I already achieved to get the top and bottom based on NUMBER ASSIGNED IN @RankedBy Parameter.

WHAT i want now, is to have a condition where, if the result set per business is BELOW OR LESS THAN THE PARAMETER @RankedBy, the result should no longer include the BOTTOM PART, like this 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 than the parameter assigned in @RankedBy which is 5.

On my existing code, what I planned is to have a count per business and put the condition in where clause, but I found it inefficient and ineffective.

If anyone among you experts could give a more efficient coding or could correct my initial codes.

THANKS IN ADVANCE

score 0 · Answer 1 · answered Jan 21 '16 at 09:49

0

I would create a temporary table with the result of the full query, then do two simple queries with ORDER BY sales LIMIT 5 and ORDER BY sales DESC LIMIT 5.

The benefit is that you can do other refinements on the temporary table if you need, like computing the mean, average,...

answered Jan 21 '16 at 09:49

manuBriot

2,755
13
21

Let's see whether I can get the syntax right the first time: `CREATE TEMP TABLE foo (tenant TEXT, business TEXT,sales INTEGER, rankName TEXT, rank INTEGER); INSERT INTO foo SELECT ...as you did; SELECT * from foo order by sales limit 5;` – manuBriot Jan 21 '16 at 09:52
LIMIT is mysql I beleive. For SQL Server `SELECT TOP 5` etc – Liesel Jan 21 '16 at 09:58
Sorry, I missed that it was for sql-server... "SELECT TOP" seems like a good candidate though, still on the temporary table. The temporary table will simplify your query, you can avoid the UNION by simply doing two inserts for instance. – manuBriot Jan 21 '16 at 10:02
I need to add a column where it will state if it is TOP or BOTTOM, that's I way I made it that way.. – rickyProgrammer Jan 21 '16 at 11:42

score 0 · Answer 2 · answered Jan 21 '16 at 09:58

you add having at the bottom part like this

  @RankedBy int = 5

    SELECT *FROM (
        SELECT #TEMPTABLE5.*, 'TOP' AS 'RankName' ,
               Rank() over (Partition BY Business ORDER BY Sales ) AS Rank
               FROM #TEMPTABLE5
        ) rs WHERE Rank <= @RankedBy 

    UNION ALL

    SELECT tenant,business,sales,rankName,rank FROM (
       select tenant,business,sales,rankName,rank,max(rank) from(
        SELECT #TEMPTABLE5.*, 'BOTTOM' AS 'RankName' ,
               Rank() over (Partition BY Business ORDER BY Sales DESC ) AS Rank
               FROM #TEMPTABLE5
        ) rs WHERE Rank <= @RankedBy)rk 
   group by tenant,business,sales,rankName,rank Having MAX(Rank) = @RankedBy

 order by  Business, RankName DESC ,Rank

Case Statement in Where Clause in SQL Server

2 Answers2