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

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.

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.

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.

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?

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.

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?