|
View previous topic :: View next topic
|
| Author |
Message |
Vignesh Sid
New User
Joined: 04 Sep 2017
Posts: 43
Location: India
|
|
|
|
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 |
|
 |
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
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 |
|
|
Vignesh Sid
New User
Joined: 04 Sep 2017
Posts: 43
Location: India
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
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 |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
| 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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| 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 
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 |
|
|
Vignesh Sid
New User
Joined: 04 Sep 2017
Posts: 43
Location: India
|
|
|
|
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! Thanks Arun and Rahul for your valuable comments on my description.
Regards,
Viky. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
Vignesh,
Welcome and Glad it worked the way you wanted and yes I read your mind . |
|
| Back to top |
|
|
Vignesh Sid
New User
Joined: 04 Sep 2017
Posts: 43
Location: India
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
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 |
|
|
Vignesh Sid
New User
Joined: 04 Sep 2017
Posts: 43
Location: India
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
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 |
|
|
|
|
