SQL query row_number() over partition by query result return wrong for some case

I have two SQL Server tables:

FormSchema columns

SchemaId, SchemaName, Tenant

RoleTable columns

SchemaId, RoleId, RoleName, Tenant

Expected result (includes below three conditions):

  • select row in result where individual SchemaName exist in Tenant 'A'
  • select row in result where individual SchemaName exist in Tenant 'All'
  • If duplicate/same SchemaName exist in Tenant 'A' and 'All' both then select row in result which belong to Tenant 'A'.

Query:

select * 
from 
    (select 
         fs1.schemaid, fs1.schemaName, ar1.roleId, ar1.roleName, ar1.tenant,
         rn = row_number() over (partition by fs1.schemaName
                                 order by case when ar1.tenant = 'ALL' then 2 else 1 end, ar1.tenant)
     from   
         RolesTable ar1
     full outer join 
         FormSchema fs1 on ar1.SchemaId = fs1.SchemaId) as t3
where 
    rn = 1
    and Tenant in ('B', 'All')

Please see the db fiddle for table, records, and expected and actual result.

The issue I am facing like while giving Tenant as 'B' expected result is wrong but instead if i use 'A' expected result is correct.

Expected result for 'B' is wrong** because it's not satisfying the following:

  • If duplicate/same SchemaName exist in Tenant 'B' and 'All' both then select row in result which belong to Tenant 'B'.i.e. SchemaName 'Car'. So query should select in result 'B' 'Car' row.

Expected Result for 'B':

SchemaId   RoleId                                   RoleName    Tenant
--------------------------------------------------------------------
'664'      '40ecca83-7fd9-4d63-9f56-c7a48442d844'   '#Test-1'   'B'
'456'      '40ecca83-7fd9-4d63-9f56-c7a48442d844'   '#Test-1'   'B'

Thanks in advance.

2 answers

  • answered 2021-06-19 14:04 Gordon Linoff

    I find the logic a bit hard to follow. And I don't understand why you are using a full join when an inner join does the work.

    However, I think what you need to do is filter in the subquery:

    select * 
    from (select fs1.schemaid, fs1.schemaName, ar1.roleId, ar1.roleName, ar1.tenant ,
                 row_number() over (partition by fs1.schemaName
                              order by case when ar1.tenant = 'ALL' then 2 else 1 end, ar1.tenant) as seqnum
          from RolesTable ar1 join
               FormSchema fs1
              on ar1.SchemaId= fs1.SchemaId
          where ar1.Tenant in ('B', 'All')
         ) t3
    where seqnum = 1 ;
    

  • answered 2021-06-19 14:05 forpas

    You should move the condition for the Tenant from the outer query inside the subquery:

    select * 
    from (
      select fs1.schemaid, fs1.schemaName, ar1.roleId, ar1.roleName, ar1.tenant,
             row_number() over (
               partition by fs1.schemaName
               order by case when ar1.tenant = 'ALL' then 2 else 1 end, ar1.tenant
             ) rn
      from RolesTable ar1 full outer join FormSchema fs1
      on ar1.SchemaId= fs1.SchemaId
      where ar1.Tenant in (?, 'All')
    ) as t3
    where rn = 1
    

    Replace ? with 'A' or 'B'.

    Also, it is not clear why you are doing a FULL join instead of a LEFT join.
    If you want in the results unmatched rows from both tables, then may be you should use COALESCE():

    where coalesce(ar1.Tenant, fs1.Tenant) in (?, 'All')
    

    See the demo.