Hi, I have two following tables:

Table1::
________________
LINK ORDER
----------------------

tom tom_first
tom tom_second
tom tom_third
tom tom_fourth
tom tom_fifth
mike mike_first
mike mike_second
mike mike_third
mike mike_fourth
mike mike_fifth
jim jim_first
jim jim_second
jim jim_third
----------------------------------


TABLE 2::

_________________________
LINK VALUE
----------------------------------

tom_fourth Tom's fourth
jim_third Jim's third
tom_first Tom's first
mike_first Mike's first
tom_third Tom's third
mike_fifth Mike's fifth
tom_second Tom's second
jim_first Jim's first
mike_third Mike's third
jim_second Jim's second
tom_fifth Tom's fifth
mike_second Mike's second
mike_fourth Mike's fourth
--------------------------------------


Now when I run the following query,

select A.NAME,B.VALUE
from DB2INST1.TABLE1 as A
INNER JOIN
DB2INST1.TABLE2 as B
ON A.ORDER=B.LINK
WHERE A.NAME='tom'

I get the following result;

NAME VALUE
tom Tom's fourth
tom Tom's first
tom Tom's third
tom Tom's second
tom Tom's fifth

What changes should I make in order to fetch the results sequentially.. I mean following TABLE1's order instead of TABLE2's.

would really appreciate some help,
thanks

you can't

reason? rows in a table do not have any order

they are lilke marbles in a bag

no but they do seem to be following a specific order... its just that they are following TABLE2s order.

add a column to TABLE1, and populate it with values that reflect the order you want the rows returned in, then use that column in the ORDER BY clause of your query

Can't write to the tables concerned rudy, not authorized Is there another way?
Another thing I wanted to know was, that in what order does a default SELECT statement fetch records? I mean is it based on timestamp i.e, earliest record inserted first? and if it is not that case can we order the results based on this timestamp?

rocker86, the ONLY way to ensure rows are returned in a specified order in any relational database is to use the ORDER BY clause. This sorts in Ascending or Descending order based on the collating sequence of the code page the database is using.

In your example, it would be impossible (to guarantee) the returned data will be in the same order as your Link_Order table as it is not in any sort order. If you used an ORDER BY the first column, Jim would be first, Mike second and Tom third. If you used ORDER BY first_column DESC, second_column ASC, the first column would be in order (Tom, Mike, Jim) but the second column would not (and it wouldn't matter if it as ASC or DESC). You would have either tom_fifth, tom_first, tom_fourth, tom_second and tom_first or the reverse of that.

To answer you other question (what order does a default Select (one without an ORDER BY) fetch rows), the answer is whatever order the database engine finds them. If you have a Clustering Index (and your data actually could be sorted in an order you wanted) on a static table where nothing changes and you do a SELECT on just the one table (and the table has been Reorged to put the data in clustering order) without a WHERE clause (or at least one that only references the columns in the Clustering index), ORDER BY, GROUP BY, DISTINCT (or anything else that could cause a Sort), the rows will (most likely) be returned in the Clustering order. This is because the data will (probably) be accessed by a Table Space scan reading the First Page, First row, second row.., Second Page, next row, etc.

Since the data is in clustering order it would be returned that way. But almost ANYTHING could cause that not to happen. If you Insert or Update the Clustering Key value, the rows affected could be put back into the table on ANY page (i.e. not in Clustering Order) and the same table space scan would NOT return the data in the actual clustering order. If a index RID sort is used the order of the rows being returned can be affected. Anything that causes a sort can change the order. The amount of data in the table could change how the data is returned. This list could go on for quite some time of the things that could affect the 'default' (there really is not such thing) order the rows are returned.

So, as r937 has already stated, If you want you data sorted and it can't be sorted based on the collating sequence, then you can NOT order your data (even with an ORDER BY clause). Your only option is to include a column that CAN be sorted. Since you stated this is not an option, you can't do what you want to do.

hmm.. I guess even the solution provided here would not be of much help either as in my case the brackets of the IN clause would contain a query instead of static values.

Anyways, thanks Stealth_DBA for the much needed explanation.