Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Fetch first first row in inner join!

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Vignesh Sid

New User


Joined: 04 Sep 2017
Posts: 43
Location: India

PostPosted: Tue Dec 04, 2018 7:25 pm    Post subject: Fetch first first row in inner join!
Reply with quote

Hi all,

I have the following three tables:
Table1:
Code:
Name      status
Viky        active
Mojo       active


Table 2:
Code:
Name           Class
Viky              Fourth
Mojo             Fourth


Table 3:
Code:
Class            Subjects
Fourth          English
Fourth          Science

Required output:
Code:
Viky        Fourth     English
Mojo       Fourth     English

Only the first row should be fetched from table 3.

I wrote the follow query:

Code:

select a.name,b.class,c.subject
  from table1 a
inner join table2 b on (
         a.name=b.name
                            )
inner join table3 c on (
         b.class=c.class
                            )
where a.status = 'active';



This query gives me:
Code:
Viky        Fourth     English
Viky        Fourth     Science
Mojo       Fourth     English
Mojo       Fourth     Science

If I use fetch first row after where condition only the first row is fetched.

Could anyone please help me in putting fetch first row condition in inner join of table3.

Thanks,
Viky
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2161
Location: UK

PostPosted: Tue Dec 04, 2018 8:48 pm    Post subject: Reply to: Fetch first first row in inner join!
Reply with quote

You do know, don't you, that the order that in which rows are returned is not necessarily the same each time? If you wan to guarantee that the first row is English (in this case) then you nedd to use ORDER. And if you want English then why not
Code:
WHERE table3.subject = 'English'
?
Back to top
View user's profile Send private message
Vignesh Sid

New User


Joined: 04 Sep 2017
Posts: 43
Location: India

PostPosted: Tue Dec 04, 2018 9:19 pm    Post subject:
Reply with quote

Hi Nic,

Let me make my requirements more clear! Sorry for not explaining in depth.

Table 3:

Code:

Class            Subjects             Status
Fourth          English               
Fourth          Science               Active
Fourth          Maths                 Active


I have to fetch the first row from table 3 which has status as active. And it is not certain that science will always be in first instance.

Desired output:

Code:

Viky        Fourth     Science
Mojo        Fourth     Science

The final query will be like:
Code:

select a.name,b.class,c.subject
  from table1 a
inner join table2 b on (
         a.name=b.name
                            )
inner join table3 c on (
         b.class=c.class
   and c.status='Active'
                            )
where a.status = 'active';


So is there any possibility to put a fetch first row command in inner join query of table3?

Tanks,
Viky[/code]
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2125
Location: NY,USA

PostPosted: Tue Dec 04, 2018 10:22 pm    Post subject:
Reply with quote

Try this
Code:
select table1.Name, table2.class, table3.subjects
from
table1 ,
 table2,
(select Class, Subjects ,
  ROW_NUMBER() OVER
  (PARTITION BY Class,Subjects ORDER BY Class,Subjects ) as pick_one
   from table3
  where status = 'active') as table 3a
 where
       table1.Name = table2.Name
and table1.status = 'active'
and  table2.Class = table3a.Class
and  table3a.pick_one = 1
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Dec 05, 2018 8:52 pm    Post subject:
Reply with quote

Maybe this would work for the OP, I don't have access to test this though.
Code:
select a.name,
       b.class,
       c.sub
  from table1 a,
       table2 b,
      (select class,min(subject) sub from table3
        where status = 'Active'
        group by class) c
where a.status = 'active'
  and a.name   = b.name
  and b.class  = c.class
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 447
Location: USA

PostPosted: Thu Dec 06, 2018 1:20 am    Post subject: Reply to: Fetch first first row in inner join!
Reply with quote

Quote:
I have to fetch the first row from table 3 which has status as active

TS needs to understand that there is No concept of first row in a DB2 table unless identified by the primary key.

Also, if you use Subject in ORDER BY then Maths should come before Science.

This leads me to doubt if TS really understands what he meant by fetching first row. Arguably, there is No first row.

.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Dec 06, 2018 2:31 am    Post subject:
Reply with quote

Quote:
This leads me to doubt if TS really understands what he meant by fetching first row. Arguably, there is No first row
Despite Nic's very first response in this thread, not sure if it really reached the TS that without any ORDER BY the row number/the order of rows returned could be arbitrary. I'll try to clean this up a bit.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2125
Location: NY,USA

PostPosted: Thu Dec 06, 2018 2:37 am    Post subject:
Reply with quote

Quote:
Despite Nic's very first response in this thread, not sure if it really reached the TS that without any ORDER BY the row number/the order of rows returned could be arbitrary
And that's exactly why RANK function used to assign numbers to the rows and pick the first one, in the solution provided ORDER by is optional, if TS take that out then rank should be assigned the way data is stored.

Quote:
This leads me to doubt if TS really understands what he meant by fetching first row. Arguably, there is No first row.
He surely meant to fetch first row ONLY that got a match and discard rest but he used poor terminology to have this noted icon_smile.gif

If I may suggest to wait for TS to respond back , we are drawing too many conclusions here. We have spent time adding comments and now cleaning up just negates that.
Back to top
View user's profile Send private message
Vignesh Sid

New User


Joined: 04 Sep 2017
Posts: 43
Location: India

PostPosted: Thu Dec 06, 2018 1:14 pm    Post subject:
Reply with quote

Hi Rohit,

Yes I agree that I did not mentioned the requirement in correct terminology. But in the first thread I mentioned that I need to fetch the first row only for a match.

And yes your solution worked well after removing order by and gave me the exact output which I needed.

Thanks much for your solution and understanding what I thought! icon_smile.gif Thanks Arun and Rahul for your valuable comments on my description.

Regards,
Viky.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2125
Location: NY,USA

PostPosted: Thu Dec 06, 2018 8:40 pm    Post subject:
Reply with quote

Vignesh,
Welcome and Glad it worked the way you wanted and yes I read your mind icon_smile.gif.
Back to top
View user's profile Send private message
Vignesh Sid

New User


Joined: 04 Sep 2017
Posts: 43
Location: India

PostPosted: Thu Dec 20, 2018 1:19 pm    Post subject:
Reply with quote

Hi Rohit,

Could you please tell me if there is any possibility in your query for a minor enhancement.

In this example stated, we get only two rows (English and Science) in inner select. We add row numbers and pick the first one.

If there are 500 or 1000 records then the inner select will create a table will all 1000 rows and adding row numbers to it which is not needed.

Please let me know if there is a way to fetch only the first row in the inner select. This would increase the efficiency of the query. I tried fetch first row (Viky) only statement in inner select, but it select only one row and neglect the next row (Mojo) with different keys.

Thanks.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2125
Location: NY,USA

PostPosted: Thu Dec 20, 2018 11:39 pm    Post subject:
Reply with quote

Vignesh,
Do you specifically want the first row when its matched or any one matching row? These two are different thing so, state clearly what is that you needed with examples to support. in the above post you will always get first matching row which is the assumption you acknowledged. Moreover have your ran it against 1000 rows to come to conclusion that it is a performance issue?
Back to top
View user's profile Send private message
Vignesh Sid

New User


Joined: 04 Sep 2017
Posts: 43
Location: India

PostPosted: Fri Dec 21, 2018 1:00 pm    Post subject:
Reply with quote

Hi Rohit,

Thanks for your response. Yes the requirement is to get only the first match and the query gives the same output. Also we ran the query in which 1000 rows will be created in the inner select table. So the query was running for around 6 minutes.

So I would like to hear from you if there are any possibilities to fetch only the first row for a match and not creating other records in inner select.

I also came across a query like below:

Code:

select p.personId, p.firstName, p.lastName
       , pe.emailAddress
  from Person as p
  left outer join lateral
     ( select pe.*
       from PersonEmail pe
       where pe.personId = p.personId
       fetch first 1 row only
     ) as pe
  on p.personId = pe.personId


In this query we could see fetch first row only is used in join.
I tried this, but -206 p.personId IS NOT VALID IN THE CONTEXT - error is coming at where condition in join.

Please let me know for your thoughts!

Thanks.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2125
Location: NY,USA

PostPosted: Fri Dec 21, 2018 5:17 pm    Post subject:
Reply with quote

It cannot make sense to use correlated query in LeFt outer join and that’s one of the check DB2 does and which is why you got that error.
Code:

Table a
Col1 col2
1234 abc
1234 Xyz
1234 acd

So my specific question to out is (since you did not bother to show with example of what first one row means ) if 1234 is matched do you want any first row like say xyz or abc or acd or you always want abc which is first row literally ? Also what is the index on this table? Is there a cluster index and what columns used for that?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SORT INNER JOIN [F1] OUT OF SEQ ERROR balaji81_k DFSORT/ICETOOL 8 Wed Jan 30, 2019 3:21 am
No new posts Dates compare on specific dates using... balaji81_k DFSORT/ICETOOL 2 Thu Jan 24, 2019 12:03 am
No new posts DB2 SQL Query to fetch all instances ... MallikarjunSM DB2 2 Thu Sep 27, 2018 6:46 pm
No new posts Join 2 File using a Join Key scorp_rahul23 DFSORT/ICETOOL 6 Tue Aug 14, 2018 7:36 am
No new posts Logic to skip Maximum time-stamp and ... vickey_dw DB2 2 Thu Feb 01, 2018 11:15 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us