use row_number() over(partition by col order by col) in where clause

All we need is an easy explanation of the problem, so here it is.

I have these tables in which I need to use Row_number() in where clause like this

where spectrumcms.staffchilddiscountpolicy.childno =
(select  ROW_NUMBER() Over (Partition by std.GuardianCNIC  order by std.ID ASC))

I have tables like these

use row_number() over(partition by col order by col) in where clause

Now i have this query

select
  std.ID as ID,
  std.FirstName,
  std.LastName,
  emp.ID as EmpID,
  emp.FirstName + '' + emp.LastName as EmpName,
  emp.CNIC,
  (
    select count(*) as NoOfChilds
      from StudentManagement.SpectrumCMS.Student stdd
      join HRManagement.SpectrumCMS.Employee empp on stdd.GuardianCNIC = empp.CNIC
      where empp.ID = emp.ID and stdd.CompanyID = std.CompanyID and empp.CompanyID = emp.CompanyID
  ) as NoOfchilds,
  ROW_NUMBER() Over (Partition by std.GuardianCNIC order by std.ID ASC) as ChildNo 

from StudentManagement.SpectrumCMS.Student std
join HRManagement.SpectrumCMS.Employee emp on std.GuardianCNIC = emp.cnic
where std.CompanyID = 20145
  and emp.CompanyID = 20145
  and std.IsActive = 1
  and emp.IsActive = 1
order by emp.CNIC

Now coming to main question why I need where=row_number()over(partition by col order by col):

Because i need to get percentage in last column of above mentioned query from the table of which i have posted the picture of.

This is what currently i am getting

use row_number() over(partition by col order by col) in where clause

Now in last percentage column, I need percentage from the staffchilddiscountpolicy table of which I have added picture

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

As mentioned in the comments, you can’t use a window function in that capacity. Rather you should take your first query and put it in a subquery or CTE such that you can then join on ChildNo between it and the SpectrumCMS.StaffChildsDiscountPolicy table like so:

WITH CTE_StudentChildNumbers AS
(
    select
        std.ID as ID,
        std.FirstName,
        std.LastName,
        emp.ID as EmpID,
        emp.FirstName + '' + emp.LastName as EmpName,
        emp.CNIC,
        (
            select count(*) as NoOfChilds
            from StudentManagement.SpectrumCMS.Student stdd
            join HRManagement.SpectrumCMS.Employee empp on stdd.GuardianCNIC = empp.CNIC
            where empp.ID = emp.ID 
                and stdd.CompanyID = std.CompanyID 
                and empp.CompanyID = emp.CompanyID
        ) as NoOfchilds,
       ROW_NUMBER() Over (Partition by std.GuardianCNIC order by std.ID ASC) as ChildNo 
   from StudentManagement.SpectrumCMS.Student std
   join HRManagement.SpectrumCMS.Employee emp on  std.GuardianCNIC = emp.cnic
   where std.CompanyID = 20145
       and emp.CompanyID = 20145
       and std.IsActive = 1
       and emp.IsActive = 1
)

SELECT 
    SCN.ID, 
    SCN.FirstName, 
    SCN.LastName, 
    SCN.EmpID, 
    SCN.EmpName, 
    SCN.CNIC, 
    SCN.NoOfchilds, 
    SCN.ChildNo,
    SCDP.Percentage
FROM CTE_StudentChildNumbers AS SCN
INNER JOIN SpectrumCMS.StaffChildsDiscountPolicy SCDP
    ON SCN.ChildNo = SCDP.ChildNo
WHERE SCDP.CompanyID = 20145 -- Not sure if you wanted the same filters from your screenshot, feel free to remove if not needed
    AND SCDP.IsActive = 1 
ORDER BY SCN.CNIC

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: ,

Leave a Reply