The sum of the recent m1Bs or m2Bs

[dotJoon]

SELECT m, MAX(maxDateTime) AS latest
  FROM ( put your UNION query here ) AS u
GROUP BY m

The code below produces an Error.

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?

[r937]

The code below produces an Error.

you tried to write something different

put the entire union query in there, inside the parentheses

[dotJoon]

put the entire union query in there, inside the parentheses

The entire union query? like the following? although it causes an error...

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

[r937]

go to post #24

you have shown code, and you have shown result

copy the entire code as your union query

[dotJoon]

go to post #24

you have shown code, and you have shown result

copy the entire code as your union query

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

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?

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.)

[dotJoon]

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.

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?

[r937]

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

[dotJoon]

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?

[r937]

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

[dotJoon]

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?

[r937]

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

[dotJoon]

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?

[r937]

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

[dotJoon]

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.

[r937]

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

[dotJoon]

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.)

[oddz]

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.

[r937]

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

[dotJoon]

in the ON clause, match them on the time

The code below causes an ERROR.

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

[r937]

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?

[dotJoon]

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.

[r937]

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 --

  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 ...

[dotJoon]

The following code also causes an ERROR.

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

[r937]

you have enough experience with derived tables to be able to figure out this error

which two tables are being joined?

[dotJoon]

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?