CODEWITHSUNDEEP
sqlsql-servert-sqljoin
cj devin
cj devin

Reputation: 1375

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):

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:

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.

Upvotes: 1

Views: 918

Answers (2)

forpas
forpas

Reputation: 164234

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271241

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 ;

Upvotes: 1

Related Questions