query difference - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

query difference

Author

Topic

subhaoviya
Posting Yak Master

135 Posts

Posted - 2014-03-28 : 06:38:23

Hi,

Many I know the below mentioned query differences?

Query1:

select c1,row_number() over(partition by c2 order by c3 desc) as selectcol2
from t1 a1
where exists(select 1 from t1 a2 where skid is null and a1.c4=a2.c4)

query 2:
select c1,row_number() over(partition by c2 order by c3 desc) as selectcol2
from t1
where skid is null

Thanks
Subha

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-28 : 09:34:07

They are logically different queries - but I did not understand what you meant by "query differences".

For one thing, in the first query, you would not get any rows where c4 is null. In the second query you may or may not depending on skid.

And, in the first query you can get rows in which skid is not null, in the second query you would not.

subhaoviya
Posting Yak Master

135 Posts

Posted - 2014-03-28 : 11:04:34

no james,

in first query inside "where exists" statement I check SKID=null,
in second query in where clause I check the same. both query implement row_number using same partition function.

I am not sure why both query produce different result.

I am confused.

thanks
subha

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-28 : 17:34:40

Run the example below and you will see what I meant:

CREATE TABLE #tmp(skid INT,c1 INT , c2 INT, c3 INT, c4 INT);
INSERT INTO #tmp VALUES
(1,1,1,1,10),
(NULL,2,1,1,10),
(NULL,3,1,1,11),
(1,4,1,1,NULL),
(NULL,5,1,1,NULL);

select c1,row_number() over(partition by c2 order by c3 desc) as selectcol2
from #tmp a1
where exists(select 1 from #tmp a2 where skid is null and a1.c4=a2.c4)

select c1,row_number() over(partition by c2 order by c3 desc) as selectcol2
from #tmp 
where skid is null

DROP TABLE #tmp;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources