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: 41
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: 2123
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: 41
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: 2064
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: 2448
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: 2448
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: 2064
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: 41
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: 2064
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
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 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
No new posts Query to skip Max time-stamp and fetc... vickey_dw DB2 8 Sun Jan 28, 2018 1:09 am
No new posts query to fetch record which has only ... maxsubrat DB2 12 Mon Dec 11, 2017 5:03 pm

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