Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    44

    Unanswered: Ordering an join query result

    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
    Last edited by rocker86; 09-19-09 at 06:57.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rocker86
    What changes should I make in order to fetch the results sequentially.. I mean following TABLE1's order instead of TABLE2's.
    you can't

    reason? rows in a table do not have any order

    they are lilke marbles in a bag
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    44
    no but they do seem to be following a specific order... its just that they are following TABLE2s order.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rocker86
    What changes should I make in order to fetch the results sequentially.. I mean following TABLE1's order instead of TABLE2's.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2009
    Posts
    44
    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?

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

  7. #7
    Join Date
    Jul 2009
    Posts
    44
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •