What is the difference between these two quries why they returning two different count. can any one explain....

---1
select count(distinct (t.id)) FROM 
(
select row_number() OVER (PArtition by s.sid order by s.sid)n, s.*  
FROM sub s inner join payee p on s.sid= p.sid

where  
	s.status in ('A','B','C','D')
and s.productid in ('m','s')
and s.billingMethod='online'

) t where t.n=1

---2	
select count(distinct (t.id)) FROM 
(
select row_number() OVER (PArtition by s.sid order by s.sid)n, s.*  
FROM sub s inner join payee p on s.sid= p.sid
) t where t.n=1
AND
s.status in ('A','B','C','D')
and s.productid in ('m','s')
and s.billingMethod='online'		

yes, it could be possible because you are only getting the row number =1(first row)  in both the cases but in the second query you are applying the fliter after feteching all the first rows in the partition and those predicates might not exist in the first row.

where as in the second one the predicate is already and then first row is pulled.

so, you should more rows in query 1 than in query 2

HI Zaim

Second query won't work (wrong syntax) where t.n=1 where s.status in ...

You might have entered the wrong code.

Hi,

First Query derived based on rownumber

select row_number() OVER (PArtition by s.sid order by s.sid)n, s.*   -----*** row number working based on where conditions
FROM sub s inner join payee p on s.sid= p.sid 

its return some value

and 

select row_number() OVER (PArtition by s.sid order by s.sid)n, s.*   -----*** row number working based on where conditions
FROM sub s inner join payee p on s.sid= p.sid
where  s.status in ('A','B','C','D')
and s.productid in ('m','s')
and s.billingMethod='online'

this one return different row number, because in this row number calculated based on where condition

then finally n=1 returns some value.

2. in second query obviously differs from first from one 

because 

select *  FROM   
(
select row_number() OVER (PArtition by s.sid order by s.sid)n, s.*  
FROM sub s inner join payee p on s.sid= p.sid
) t  

 in this query row number calculated and consider as t table so row number wont change.

select *  FROM   
(
select row_number() OVER (PArtition by s.sid order by s.sid)n, s.*  
FROM sub s inner join payee p on s.sid= p.sid
) t 

where t.n = 1
and t.status in ('A','B','C','D')
and t.productid in ('m','s')
and t.billingMethod='online'

so after adding where clause the count should differs.

Note: execute step by step , then you can get clear idea.

thanks & Regards,

gvrspk veni.

• Edited by gvrspk veni 2 hours 3 minutes ago
• Proposed as answer by pituachModerator 2 hours 1 minutes ago

Good day Zaim Raza,

You can get the best answer directly from your SQL Server by looking at the execution plan that each query return.

* without DDL+DML we can not do the same, by the way.

To check the execution plan check this image:

* basically first query you filter the data first in the subquery as part of the join query, while in the second query you try to filter the data in the external query. but this query is not well formated and can not be execute as it is. As other mentioned first you need to fix the query and then you will see the answer yourself in the execution plan (filter during the JOIN and row_number and in the external query can lead to different