Select top 50 rows in sql server

Question

I have below T-SQL code used in SQL Server 2014.

This code produces 1000s of rows. But i need only top 50 rows (from Supplier Column) from this.

In the below code, if I use SELECT Top 50 s.[CusNo] Supplier then I am not getting desired results.

What changes needs to be done in the below code in order to get only Top 50 rows (of Supplier column) with out any change in the existing result.

SELECT s.[CusNo] Supplier, 
RTRIM(CAST(s.[Customer] AS VARCHAR(50)) ) AS Name,
s.[ConcessionNo] Concession, 
RTRIM(CAST(s.[ConcessionName] AS VARCHAR(50)) ) AS ConcessionName,

sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 5) 
           then s.SELLINC else 0 end) ActualSales,

     sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) -1 /* On 14/Feb/2021 modify +6 to -1 */) and convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
           then s.SELLINC else 0 end) LastYrVariance,

     (sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 5) then s.SELLINC else 0 end))-

     (sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) -1 /* On 14/Feb/2021 modify +6 to -1 */) and convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */) then s.SELLINC else 0 end)) LastYrVariancePounds,

     (IsNull(sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 5) 
           then s.SELLINC else 0 end)-sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) -1 /* On 14/Feb/2021 modify +6 to -1 */) and convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
           then s.SELLINC else 0 end),0)/NullIf(sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) -1 /* On 14/Feb/2021 modify +6 to -1 */) and convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
           then s.SELLINC else 0 end),0))*100 LastYrVariancePercentage,


    sum(case when s.Date 
        BETWEEN         
             convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(GetDate()) = 1 THEN CONVERT(VARCHAR(4), GetDate(), 112) - 1 ELSE CONVERT(VARCHAR(4), GetDate(), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
        AND        
             Convert(date, dateadd(wk, datediff(wk, 0, GETDATE()) - 1, 0) + 5)       
           then s.SELLINC else 0 end) YrToDateActual,


    sum(case when s.Date    
        BETWEEN  
           convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(DATEADD(year,-1,GetDate())) = 1 THEN CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) - 1 ELSE CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
        AND
           convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
          then s.SELLINC else 0 end) LastYrToDateActual,


    (sum(case when s.Date 
     BETWEEN        
             convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(GetDate()) = 1 THEN CONVERT(VARCHAR(4), GetDate(), 112) - 1 ELSE CONVERT(VARCHAR(4), GetDate(), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
        AND        
             Convert(date, dateadd(wk, datediff(wk, 0, GETDATE()) - 1, 0) + 5) 
           then s.SELLINC else 0 end))  

     -

    (sum(case when s.Date 
        BETWEEN  
           convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(DATEADD(year,-1,GetDate())) = 1 THEN CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) - 1 ELSE CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
        AND
           convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
           then s.SELLINC else 0 end)) YrToDateVariancePounds,


    ((IsNull    
         (                   
            (sum(case when s.Date 
             BETWEEN        
             convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(GetDate()) = 1 THEN CONVERT(VARCHAR(4), GetDate(), 112) - 1 ELSE CONVERT(VARCHAR(4), GetDate(), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
        AND        
             Convert(date, dateadd(wk, datediff(wk, 0, GETDATE()) - 1, 0) + 5)
           then s.SELLINC else 0 end))  

     -

    (sum(case when s.Date 
        BETWEEN  
           convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(DATEADD(year,-1,GetDate())) = 1 THEN CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) - 1 ELSE CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
        AND
           convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
           then s.SELLINC else 0 end))

          ,0)

      ) 

      /

    (NullIf 
      (
         sum(case when s.Date 
            BETWEEN  
           convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(DATEADD(year,-1,GetDate())) = 1 THEN CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) - 1 ELSE CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
        AND
           convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
           then s.SELLINC else 0 end)          

       ,0)))*100 LastYrToDateVariancePercentage

FROM [dbo].[CustomerReports] s
WHERE s.BRN = 1 or s.BRN = 2 or s.BRN = 3 or s.BRN = 4 or s.BRN = 5  or s.SELLINC is null or s.SELLINC = '0'
GROUP BY s.[CusNo], s.[Customer], s.ConcessionNo, s.ConcessionName
order by YrToDateActual desc

When I run this Query In SSRS I get below results (Which is Correct). But displays all the data.

if I use SELECT Top 50 s.[CusNo] Supplier (Rest of the code is same) then I am getting the below,

So please see the highlighted section in both the images. When I add Top 50 in SQL code then few rows (in 3rd column ) which were present in 1st image is not present in 2nd image.

See my answer to your other question: http://stackoverflow.com/a/42652957/1593840 — Will, Mar 10 '17 at 15:18

score 0 · Answer 1 · answered Mar 07 '17 at 22:43

0

Try this:

With cte as (**your long query goes here**) 
Select Top(50) * From cte

answered Mar 07 '17 at 22:43

jfatal

245
1
3

score 0 · Answer 2 · answered Mar 07 '17 at 22:44

The sort order in your query is by YrToDateActual desc, so the TOP 50 is giving you the highest 50 YrToDateActual amounts. Your SSRS report is grouping by Supplier number and name, but doesn't look to be sorting by either of these columns, so I'm guessing that the order of these suppliers is somewhat random in the SSRS output. In this case I don't think you're ever going to be able to get the first 50 rows of your original SSRS output. The best you could do would be to also sort your SSRS output (e.g. by Supplier number) and then add this sort into your query (order by Supplier, YrToDateActual desc). Adding the TOP 50 into your query would cut off your new sorted SSRS output after the first 50 results.

Alan Schofield · Answer 3 · 2017-03-08T21:36:41.457

What do you want to see in the report? Do you want to see the top 50 suppliers based on their total value (e.g. Actual £ Total) and then all the Concessions (as per your first screenshot) ?

If so, you will need to rank the suppliers by their total and select any that are rank 50 or below.

Here's a very simplified example that might help. In this I've used SUM() OVER() rather than using GROUP BY but this means you might want to change the SELECT to SELECT DISTINCT depending on you needs as you will get lots of duplicate rows. Obviously it's not got your lengthy case statements in but you should be able to adapt it easily.

SELECT * FROM 
    (
        SELECT
            x.*
            , DENSE_RANK() OVER(ORDER BY SupplierTotal DESC) as SupplierRank
            , DENSE_RANK() OVER(PARTITION BY SupplierID ORDER BY ConcessionTotal DESC) as ConcessionRank
         FROM 
            (    SELECT DISTINCT
            SupplierID, ConcessionID, MyOtherRequiredColumns,
            SUM(myValue) OVER(PARTITION BY SupplierID) AS SupplierTotal,
            SUM(myValue) OVER(PARTITION BY SupplierID, ConcessionID) AS ConcessionTotal
            FROM MyTable
        ) x
    ) z
    WHERE SupplierRank <= 50

This will also rank the concessions within supplier which might be useful..

You will also have to change you report where you have summed the values up (e.g. 99,389 in Actual £ column). Instead of summing the rows below, change the expression to be =FIRST(Fields!SupplierTotal.Value)

Select top 50 rows in sql server

3 Answers3