March 15, 2013 at 12:25 am
Hi,
Please find below query,
tab1 - Col1 int, Col2 varchar2(30)
tab2 - Col1 int, col2 varchar2(30) , col3 datetime
Query 1:
select a.col1,a.col2,b.col3
from tab1 a
full outer join tab2 b
on a.col1=b.col1
and b.col3 > '19000101'
Query 2:
select a.col1,a.col2,b.col3
from tab1 a
full outer join (select col1,col2,col3,
row_number() over(partition by col1,col3 order by col3) as rownumber
from tab2) b
on a.col1=b.col1
and b.col3 > '19000101'
Please see the above two queries,
Issue : I am getting row difference between this two queries.
for example while executing first query , getting output 3 rows.
while executing second query , getting output 5 rows.
Can you please explain me?
March 15, 2013 at 12:44 am
can you provide the sample data to which is being in used in above queries
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 15, 2013 at 2:26 am
Looks like whatever is causing the inconsistency has been lost in the simplification process. Try this:
-- Query 3:
SELECT a.col1, a.col2, b.col3
FROM tab1 a
FULL OUTER JOIN (
SELECT
col1,
col2,
col3--,
--row_number() over(partition by col1,col3 order by col3) as rownumber
FROM tab2
) b
ON a.col1 = b.col1
AND b.col3 > '19000101'
Desimplified, of course.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 15, 2013 at 4:14 am
Addtionally why are you using row_number() over(partition by col1,col3 order by col3) as rownumber
if you are not using it ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 15, 2013 at 6:52 am
Hi Sanandh ,
I Don't See any difference between two sql queries .I want to know what sample data are you using.
create table tab1(id int,name varchar(30));
create table tab2(id int,namel varchar(30),bdate datetime);
insert into tab1 values(1,'a'),(2,'b'),(3,'c');
insert into tab2 values(1,'z','2000-01-01'),(2,'y','2009-09-09'),(3,'x','2008-02-02');
select * from tab1 a full outer join tab2 b on a.id=b.id and b.bdate>'20050909'
select * from tab1 a full outer join (select id,bdate,namel,row_number() over(partition by id,bdate order by namel) as rownumber from tab2) b on a.id=b.id and b.bdate>'20050909';
select * from tab1;
select * from tab2;
select id,bdate,namel,row_number() over(partition by id,bdate order by namel) as rownumber from tab2 a;
select a.id,a.name,b.bdate from tab1 a full outer join tab2 b on a.id=b.id and b.bdate>'20050909';
select a.id,a.id,b.bdate from tab1 a full outer join (select id,bdate,namel,row_number() over(partition by id,bdate order by namel) as rownumber from tab2) b on a.id=b.id and b.bdate>'20050909';
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy