How Do I ORDER Result Sets for each Column using ASC/DESC on GROUP BY GROUPING SETS

Question

Please show me what I am doing wrongly.

I want to use the ROW_NUMBER function to generate ItemNo to a Table Record in ASCENDING order. However, I want the PercentageCover to be in DESCENDING ORDER. How can I get it done? The SQL Script is shown below with the result.

Query

SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ItemNo, a.PolicyRefCode, COALESCE(d.ShortDescription, ' ') AS PolicyName, COALESCE(a.DebitNoteNo,'') AS DebitNoteNo, COALESCE(b.Name, 'TOTAL') As InsurerName, COALESCE(a.GrossAmount, '0') AS GrossAmount, SUM(a.PercentageCover) AS PercentageCover, COALESCE(a.InsuerLeadCode,' ') AS InsuerLeadCode, SUM(a.PremiumAmount) AS PremiumAmount, SUM(a.NAICOMLevyAmount) AS NAICOMLevyAmount, SUM(a.OverridenCommissionAmount) AS OverridenCommissionAmount, SUM(a.TotalCommission) AS TotalCommission,  SUM(a.NetPremium) AS NetPremium  FROM Underwriting_InsurerPolicyDistribution As a   
INNER JOIN Underwriting_Insurer_Branch As b ON a.InsBranchCode = b.InsBranchCode  
INNER JOIN Underwriting_Policy_Code As d ON a.PolicyCode = d.PolicyCode  WHERE a.DebitNoteNo='DN0000003042'  
GROUP BY GROUPING SETS((a.PolicyRefCode, a.PercentageCover, a.DebitNoteNo, a.InsuerLeadCode, b.Name, a.GrossAmount, d.ShortDescription), (a.PolicyRefCode))  
ORDER BY a.PercentageCover DESC, ItemNo ASC, PolicyName ASC,  PremiumAmount DESC,  a.InsuerLeadCode DESC,  GrossAmount DESC,  a.PolicyRefCode DESC  
  

OutPut

Challenges

1. How do I sort the ItemNo in ASCENDING order of 1-10 ?
2. Since ItemNo 10(now 4) shall be the Total, I don't need a Row Number and the PolicyRefCode, how do I stop the ROW numbering and the PolicyRefCode on the 9th row so that numbers 10(now 4) and 0000002311 will not be added to the Last Row.

Thank you for your anticipated quick response.

Answer 1

Hi @abiodunajai

1) How do I sort the ItemNo in ASCENDING order of 1-10 ?

You should not use ORDER BY NEWID() in the ROW_NUMBER function, cause NEWID() returns random numbers.
You need to change it to ROW_NUMBER() OVER (ORDER BY SUM(a.PercentageCover) DESC) AS ItemNo

2) Since ItemNo 10(now 4) shall be the Total, I don't need a Row Number and the PolicyRefCode, how do I stop the ROW numbering and the PolicyRefCode on the 9th row so that numbers 10(now 4) and 0000002311 will not be added to the Last Row.

Try this solution:

;WITH CTE AS  
(  
 SELECT ROW_NUMBER()OVER(ORDER BY SUM(a.PercentageCover) DESC) AS ItemNo  
       ,a.PolicyRefCode  
       ,d.ShortDescription AS PolicyName  
       ,a.DebitNoteNo AS DebitNoteNo  
       ,b.Name As InsurerName  
       ,a.GrossAmount AS GrossAmount  
       ,SUM(a.PercentageCover) AS PercentageCover  
       ,a.InsuerLeadCode AS InsuerLeadCode  
       ,SUM(a.PremiumAmount) AS PremiumAmount  
       ,SUM(a.NAICOMLevyAmount) AS NAICOMLevyAmount  
       ,SUM(a.OverridenCommissionAmount) AS OverridenCommissionAmount  
       ,SUM(a.TotalCommission) AS TotalCommission  
       ,SUM(a.NetPremium) AS NetPremium    
 FROM Underwriting_InsurerPolicyDistribution As a   
      INNER JOIN Underwriting_Insurer_Branch As b ON a.InsBranchCode = b.InsBranchCode  
      INNER JOIN Underwriting_Policy_Code As d ON a.PolicyCode = d.PolicyCode    
 WHERE a.DebitNoteNo='DN0000003042'  
 GROUP BY a.PolicyRefCode,a.PercentageCover,a.DebitNoteNo,a.InsuerLeadCode,b.Name,a.GrossAmount,d.ShortDescription  
)  
SELECT COALESCE(CAST(ItemNo AS VARCHAR(20)),'')AS ItemNo  
      ,COALESCE(PolicyRefCode,'')AS PolicyRefCode  
      ,COALESCE(PolicyName,'')AS PolicyName  
      ,COALESCE(DebitNoteNo,'')AS DebitNoteNo  
      ,COALESCE(InsurerName,'TOTAL')AS InsurerName  
      ,COALESCE(GrossAmount,'0')AS GrossAmount  
      ,SUM(PercentageCover)AS PercentageCover  
      ,COALESCE(CAST(InsuerLeadCode AS VARCHAR(20)),'')AS InsuerLeadCode  
      ,SUM(PremiumAmount)AS PremiumAmount  
      ,SUM(NAICOMLevyAmount)AS NAICOMLevyAmount  
      ,SUM(OverridenCommissionAmount)AS OverridenCommissionAmount  
      ,SUM(TotalCommission)AS TotalCommission  
      ,SUM(NetPremium)AS NetPremium  
FROM CTE  
GROUP BY GROUPING SETS((ItemNo,PolicyRefCode,PolicyName,DebitNoteNo,InsurerName,GrossAmount,InsuerLeadCode),())  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.