SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Thread: order by t

  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    order by t

    Code:
    data in myTable
    
    (id) mom geo
    
    (0) 0 earth
    (1) 0 North America
    (2) 1 America
    (3) 0 Europe
    (4) 2 L.A.
    (5) 2 New York
    (6) 5 Manhatan
    (7) 3 France
    (8) 7 Paris
    I have data in myTable like the above.

    When variables.V is (2), The SQL below produces the result below.
    Code:
    sql
    select DISTINCT t1.id, t1.mom, t1.geo
      from myTable t1   
     
      join myTable t2
      on t1.id = t2.mom 
     
      join myTable t3
      on t2.id = t3.mom 
      where  t1.id = #variables.V# or
                t2.id = #variables.V# or
                t3.id = #variables.V# 
            order by id
    </cfquery>
    
    result
    
    (0) 0 earth
    (1) 0 North America
    (2) 1 America
    So far, so good.
    But let's suppose (ID) order is not ordered like the below.

    Code:
    data in myTable1
    
    (id) mom geo
    
    (0) 0 earth
    (2) 0 North America
    (1) 2 America
    (3) 0 Europe
    (4) 1 L.A.
    (5) 1 New York
    (6) 5 Manhatan
    (7) 3 France
    (8) 7 Paris
    When variables.V is 1 with the data in myTable1,
    The code below produces the result below.

    Code:
    sql
    select DISTINCT t1.id, t1.mom, t1.geo
      from myTable1 t1   
     
      join myTable1 t2
      on t1.id = t2.mom 
     
      join myTable1 t3
      on t2.id = t3.mom 
      where  t1.id = #variables.V# or
                t2.id = #variables.V# or
                t3.id = #variables.V# 
            order by id
    </cfquery>
    
    result
    
    (0) 0 earth
    (2) 1 America
    (1) 0 North America
    How can I get my target result below with the data in myTable1?
    Code:
    target result
    
    (0) 0 earth
    (1) 0 North America
    (2) 1 America
    "order by t" was one of my trials, but it's failed.
    If I remove ORDER CLAUSE, the order seems arbitrary.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dotJoon View Post
    result

    (0) 0 earth
    (2) 1 America
    (1) 0 North America
    sorry, no, that is ~not~ the result

    the result is
    Code:
    id  mom  geo
     0   0   earth
     2   0   North America
    once again, you present a problem but your data is deficient

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    sorry, no, that is ~not~ the result

    the result is
    Code:
    id  mom  geo
     0   0   earth
     2   0   North America
    Code:
    select DISTINCT t1.id, t1.mom, t1.geo
      from myTable1 t1   
     
      join myTable1 t2
      on t1.id = t2.mom 
     
      join myTable1 t3
      on t2.id = t3.mom 
      where  t1.id = #variables.V# or
                t2.id = #variables.V# or
                t3.id = #variables.V# 
            order by id
    With the sql above when variables.V is (1), I am afraid as I check twice or more, the result is
    Code:
    (0) 0 earth
    (1) 0 North America
    (2) 1 America

    Quote Originally Posted by r937 View Post
    once again, you present a problem but your data is deficient
    Do you mean the data is in myTable is not deficient, but the data in myTable1 is deficient?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, when i ran my first test on your data, i was using variable.v = 2

    i just tried it again, this time with variable.v = 1
    Code:
    id  mom  geo
     0   0   earth
     1   2   America
     2   0   North America
    as far as i can tell, the ORDER BY is working correctly

    however, the results are ~not~ the same as yours

    once again i give up on your problem, i'm far too confused

    good luck
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    okay, when i ran my first test on your data, i was using variable.v = 2
    I guessed.



    Quote Originally Posted by r937 View Post
    Code:
    id  mom  geo
     0   0   earth
     1   2   America
     2   0   North America
    however, the results are ~not~ the same as yours
    Yes, your result is right.
    I made a mistake, my result above was actually my target result.


    Quote Originally Posted by r937 View Post
    once again i give up on your problem, i'm far too confused
    I am very sorry for making you confused.

    Quote Originally Posted by r937 View Post
    good luck
    Don't go, please.
    Although you go now, I am expecting you come back later.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dotJoon View Post
    ... I am expecting you come back later.
    yes, i plan to be at sitepoint for quite some time

    however, for the third or fourth time, i have decided i'm not helping you any more
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    however, for the third or fourth time, i have decided i'm not helping you any more
    I am afraid of the pain caused by love or thought which is one-sided.

    Whether you decide helping me or not, it's up to your mind.

    Waiting is still my job.

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by dotJoon View Post
    Waiting is still my job.
    Wow, and do they pay you for that?


    Code:
    data in myTable1
    
    (id) mom geo
    
    (0) 0 earth
    (2) 0 North America
    (1) 2 America
    Code:
    target result
    
    (0) 0 earth
    (1) 0 North America
    (2) 1 America
    I don't understand. Look at the table data and target data you provided. You want to change the value of id and mom for North America and America? How? What is the rule you want to implement?

  9. #9
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    You want to change the value of id and mom for North America and America?
    No, I don't want to change the value of id and mom for North America and America.
    I just like to make the order correctly in the order of "earth - North America - America," although the ID of North America is made later than America.



    Quote Originally Posted by guido2004 View Post
    Code:
    data in myTable1
    
    (id) mom geo
    
    (0) 0 earth
    (2) 0 North America
    (1) 2 America
    Code:
    target result
    
    (0) 0 earth
    (1) 0 North America
    (2) 1 America
    The target result above is not the target result what I want.
    The target result below is what I want.

    Code:
    target result
    
    (0) 0 earth
    (2) 0 North America
    (1) 2 America
    I like to put "(2) North America" between "(0) earth" and "(1) America" although the ID(2) order of it is the last compared with the ID(0) of earth and the ID(1) of America.

    I am sorry, I made some mistake again in writing my target result in the first.
    I think that's the reason why rudy was confused.


    Quote Originally Posted by guido2004 View Post
    Wow, and do they pay you for that?
    Yes, they usually pay for it. For the most frquently, rudy do.

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    I like to put "(2) North America" between "(0) earth" and "(1) America" although the ID(2) order of it is the last compared with the ID(0) of earth and the ID(1) of America.
    Why? What is the rule you want to apply so it will always work? What is the logic?

  11. #11
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Why? What is the rule you want to apply so it will always work? What is the logic?
    Let's suppose like the following.
    God created the world (earth:id=0) from nothing(mom=0).
    I record the event into my database like the following.
    Code:
    (0) 0 earth
    People found North America(id=1) in the earth(mom=0).
    I record the event into my database like the following.
    Code:
    (1) 0 North America
    People make America(id=2) in the area of North America(mom=1).
    I record the event into my database like the following.
    Code:
    (2) 1 America
    Now I have data like the following.
    Code:
    (0) 0 earth
    (1) 0 North America
    (2) 1 America
    When variables.V is 2, the sql below produces the result below.
    Code:
    select DISTINCT t1.id, t1.mom, t1.geo
      from myTable t1   
     
      join myTable t2
      on t1.id = t2.mom 
     
      join myTable t3
      on t2.id = t3.mom 
      where  t1.id = #variables.V# or
                t2.id = #variables.V# or
                t3.id = #variables.V# 
            order by id
    
    result
    (0) 0 earth
    (1) 0 North America
    (2) 1 America
    So far so good.

    The following is another version(myTable1) of GEO history.

    God created the world (earth:id=0) from nothing(mom=0).
    I record the event into my database like the following.
    Code:
    (0) 0 earth
    People make America(id=1) in the area of some part of North of the earth(mom=2).
    I record the event into my database like the following.
    Code:
    (1) 2 America
    People calls the area of near America including America "North America(id=2)".
    I record the event into my database like the following.
    Code:
    (2) 0 North America
    Now I have data like the following.
    Code:
    (0) 0 earth
    (1) 1 America
    (2) 0 North America
    When variables.V is 2, the sql below produces the result below.
    Code:
    select DISTINCT t1.id, t1.mom, t1.geo
      from myTable1 t1   
     
      join myTable1 t2
      on t1.id = t2.mom 
     
      join myTable1 t3
      on t2.id = t3.mom 
      where  t1.id = #variables.V# or
                t2.id = #variables.V# or
                t3.id = #variables.V# 
            order by id
    
    result
    (0) 0 earth
    (1) 1 America
    (2) 0 North America
    I like to make my target result below with variables.V.

    Code:
    target result
    (0) 0 earth
    (2) 0 North America
    (1) 1 America

  12. #12
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Now I have data like the following.

    Code:
    (0) 0 earth
    (1) 1 America
    (2) 0 North America
    No you do not.
    You have
    Code:
    (0) 0 earth
    (1) 2 America
    (2) 0 North America
    Anyway, try
    Code:
    ORDER BY mom, id

  13. #13
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    No you do not.
    You have
    Code:
    (0) 0 earth
    (1) 2 America
    (2) 0 North America
    Yes, your are right. It was,Sorry, my careless.


    Quote Originally Posted by guido2004 View Post
    Code:
    ORDER BY mom, id
    Yes, that will work fine with data in myTable1.

    What about with data in myTable2.

    Code:
    data in myTable2
    
    (0) 0 earth
    (1) 2 New York
    (2) 3 America
    (3) 0 North America
    ORDER BY MOM will produce the result2 below.
    Code:
    result2
    
    (0) 0 earth
    (3) 0 North America
    (1) 2 New York
    (2) 3 America
    My target result2 is like the below.
    Code:
    target result2
    
    (0) 0 earth
    (3) 0 North America
    (2) 3 America
    (1) 2 New York
    In order to get my target result2, I guess, ORDER should relate BY t something like the following.
    Code:
    ORDER BY T1.mom, T2.mom, T3.mom


Bookmarks

Posting Permissions

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