SitePoint Sponsor

User Tag List

Page 2 of 3 FirstFirst 123 LastLast
Results 26 to 50 of 62
  1. #26
    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
    Code:
    SELECT m, MAX(maxDateTime) AS latest
      FROM ( put your UNION query here ) AS u
    GROUP BY m
    The code below produces an Error.

    Code:
    SELECT m, MAX(maxDateTime) AS latest
      FROM  m1 as m union m2 as m  AS u
    GROUP BY m
    I guess I never did UNION in FROM clause before.
    Where should I put myTable name "journal" inside red part?

  2. #27
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dotJoon View Post
    The code below produces an Error.
    you tried to write something different

    put the entire union query in there, inside the parentheses
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #28
    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
    put the entire union query in there, inside the parentheses
    The entire union query? like the following? although it causes an error...

    Code:
    SELECT m, MAX(maxDateTime) AS latest
      FROM  (m, MAX(maxDateTime) as latest from journal ) AS u
    GROUP BY m

  4. #29
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    go to post #24

    you have shown code, and you have shown result

    copy the entire code as your union query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #30
    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
    go to post #24

    you have shown code, and you have shown result

    copy the entire code as your union query
    Code:
    code
    
    SELECT m, MAX(maxDateTime) AS latest
      FROM  (select m1 as m, max(dateTime) as maxDateTime
    from journal
    group by m1
    
    union
    
    select m2 as m, max(dateTime) as maxDateTime
    from journal
    group by m2 ) AS u
    GROUP BY m
    
    result
    
    1 200911241846 
    2 200911241557 
    3 200911220132 
    4 200911281930 
    5 200911281930 
    6 200911241557
    The code above works beautiful. Thank you for that.

    Now I change the code above slightly for get the current balance status like the following.

    Code:
    code
    
    SELECT m, Bs, MAX(maxDateTime) AS latest
      FROM  (select m1 as m, m1Bs as Bs, max(dateTime) as maxDateTime
    from journal
    group by m1
    
    union
    
    select m2 as m, m2Bs as Bs, max(dateTime) as maxDateTime
    from journal
    group by m2 ) AS u
    GROUP BY m
    
    result
    
    m balance  time
    1  -90    200911241846 
    2  100    200911241557 
    3 -120    200911220132 
    4   40    200911281930 
    5   60    200911281930 
    6 -300    200911241557
    But my target result is the following.
    What's wrong in my code above?
    And How can I get my target result below?
    Code:
    target result
    
    m balance  time
    1  -90    200911241846 
    2  520   200911241557 
    3 -120    200911220132 
    4  -70    200911281930 
    5   60    200911281930 
    6 -300    200911241557
    (I must go out in an hour or two hours when my wife wake up and can't sit in front of computer for a day or two days.
    I like to get my target result before my going out.

    If I don't get my target result before my going out, I'll see you a day or two days later.)

  6. #31
    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)
    Code:
    code
    
    SELECT m, balance, MAX(maxDateTime) AS latest
      FROM  (select m1 as m, m1Bs as balance, max(dateTime) as maxDateTime
    from journal
    group by m1
    
    union
    
    select m2 as m, m2Bs as balance, max(dateTime) as maxDateTime
    from journal
    group by m2 ) AS u
    GROUP BY m
    
    result
    
    m balance  time
    1  -90    200911241846 
    2  100    200911241557 
    3 -120    200911220132 
    4   40    200911281930 
    5   60    200911281930 
    6 -300    200911241557
    Balance in the code above seems not to connect to latest for getting my target result below.

    Code:
    m balance  time
    1  -90    200911241846 
    2  520   200911241557 
    3 -120    200911220132 
    4  -70    200911281930 
    5   60    200911281930 
    6 -300    200911241557
    How can I make balance to connect to latest?

  7. #32
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you did not copy your UNION query exactly as i suggested

    instead, you added the m1Bs and m2Bs columns inside the UNION query

    go back and take them out, and just use the exact query as in post #24

    thank you

    there is another step after this but you must do each step correctly before going to the next one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #33
    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)
    Code:
    code
    
    SELECT m, MAX(maxDateTime) AS latest
    FROM  (select m1 as m, max(dateTime) as maxDateTime
    from journal
    group by m1
    
    union
    
    select m2 as m, max(dateTime) as maxDateTime
    from journal
    group by m2 ) AS u
    GROUP BY m
    
    result
    
    1 200911241846 
    2 200911241557 
    3 200911220132 
    4 200911281930 
    5 200911281930 
    6 200911241557
    Okay, I am at the stage of the above.
    What is next?

  9. #34
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dotJoon View Post
    Okay, I am at the stage of the above.
    What is next?
    excellent

    what is next is for you to explain exactly what this query produces

    in words

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

  10. #35
    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
    excellent

    what is next is for you to explain exactly what this query produces

    in words

    It produces each member's latest transaction time.

    Do you mean I should get the latest balance while the query is turning in server language?

  11. #36
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, we are making good progress

    do you understand how the results of a query can be used in another query?

    the example is a union query, and the results of the union query are used in another query, where the union query is a subquery in the other query's FROM clause

    this concept is called derived table, do you understand that? google it if you are not sure
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #37
    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, we are making good progress

    do you understand how the results of a query can be used in another query?

    the example is a union query, and the results of the union query are used in another query, where the union query is a subquery in the other query's FROM clause

    this concept is called derived table, do you understand that? google it if you are not sure
    Yes, I understand it thanks to you. And I'll remember the term derived table.

    Now, what's next step?

  13. #38
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    next step is to use the results of the query in post #33 to chose which rows of the original table you need

    remember, this query in post #33 gives you the latest time per member

    how do these times relate to the times in th original table?

    try to understand it before you write the SQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #39
    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
    next step is to use the results of the query in post #33 to chose which rows of the original table you need

    remember, this query in post #33 gives you the latest time per member

    how do these times relate to the times in th original table?

    try to understand it before you write the SQL
    I need the sum of mBs.

    mBs is one of m1Bs and m2Bs.
    m1Bs or m2Bs which is relate to m1 or m2.

    If m is m1 in the result of the query in post #33, mBs is m1Bs.
    If m is m2 in the result of the query in post #33, mBs is m2Bs.

  15. #40
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    let me try to ask again...

    how do the times from the query in post #33 relate to the times in th original table?

    imagine each row of the results from the query in post #33 somehow matching a row in the original table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #41
    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
    How Do The times from The Query In Post #33 Relate To The times in Th Original Table?

    Imagine each Row Of The Results from The Query In Post #33 Somehow matching A Row In The Original Table
    The times from the Query In Post #33 is the latest time of the member number has the transaction.

    The times in the Original table is the time that two member numbers have transaction each other.

    How do they relate?
    They don't relate directly.
    They relate indirectly through member number.


    (I am afraid this is not the answer what you want.)

  17. #42
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    What you need to accomplish in concept is this:

    http://dev.mysql.com/doc/refman/5.0/...group-row.html

    Now try to apply that to your own problem.

  18. #43
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dotJoon View Post
    How do they relate?
    They don't relate directly.
    they relate directly through the time

    the original table has muiltiple rows for each member

    the result from query in post #33 is a table which has only one row for each member, namely, the latest time

    if you match them on the time, then each row from the query in post #33 will match with only one row from the original table, namely the one with the latest time

    you need to do a join between the original table and the derived table of results from the query in post #33

    remember, we took multiple posts here in this thread but were eventually successful in getting you to fully understand what a derived table is

    so now, write the join between this derived table and your original tble

    in the ON clause, match them on the time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #44
    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
    in the ON clause, match them on the time
    The code below causes an ERROR.
    Code:
    SELECT m, MAX(maxDateTime) AS latest
    FROM  (select m1 as m, max(dateTime) as maxDateTime
    from journal
    group by m1
    
    union
    
    select m2 as m, max(dateTime) as maxDateTime
    from journal
    group by m2 ) AS u
    left join journal on u.latest=journal.time
    
    GROUP BY m

  20. #45
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you did the join correctly

    however, i am curious why you thought you need another GROUP BY

    the "u" derived table already calculated the latest time for each member, so you don't have to do it again

    also, which columns did you want to retrieve in the SELECT clause?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #46
    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
    you did the join correctly
    No, it causes an Error saying "Unknown column 'u.latest' in 'on clause'"

    I guess I did not the join correctly.

  22. #47
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yeah, you're right, you didn't do it correctly, i am sorry, i thought you did but you didn't

    your join should look like this --
    Code:
      FROM (
            SELECT m, MAX(maxDateTime) AS latest
              FROM (
                   select m1 as m, max(dateTime) as maxDateTime
                     from journal
                   group by m1
                   union
                   select m2 as m, max(dateTime) as maxDateTime
                     from journal
                   group by m2 
                   ) AS u
            GROUP BY m
            ) AS d
    INNER
      JOIN ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #48
    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)
    The following code also causes an ERROR.
    Code:
    select m, latest
      (SELECT m, MAX(maxDateTime) AS latest
      FROM  (select m1 as m, max(dateTime) as maxDateTime
                     from journal
                     group by m1
    
                     union
    
                     select m2 as m, max(dateTime) as maxDateTime
                     from journal
                     group by m2 ) AS u
      GROUP BY m ) as d
    inner join journal on u.latest=journal.time

  24. #49
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you have enough experience with derived tables to be able to figure out this error

    which two tables are being joined?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #50
    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
    which two tables are being joined?
    table u and table journal.
    Should I take other table join combination like u and d or d and journal?


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
  •