SitePoint Sponsor |
|
User Tag List
Results 26 to 50 of 62
-
Dec 4, 2009, 11:53 #26
-
Dec 4, 2009, 11:55 #27
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Dec 4, 2009, 12:10 #28
-
Dec 4, 2009, 12:23 #29
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Dec 4, 2009, 12:50 #30
- 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
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
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 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.)
-
Dec 4, 2009, 13:29 #31
- 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
Code:m balance time 1 -90 200911241846 2 520 200911241557 3 -120 200911220132 4 -70 200911281930 5 60 200911281930 6 -300 200911241557
-
Dec 4, 2009, 14:42 #32
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Dec 4, 2009, 15:05 #33
- 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
What is next?
-
Dec 4, 2009, 18:37 #34
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Dec 5, 2009, 12:42 #35
-
Dec 5, 2009, 13:11 #36
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Dec 5, 2009, 15:35 #37
-
Dec 5, 2009, 17:15 #38
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Dec 5, 2009, 17:37 #39
-
Dec 5, 2009, 20:18 #40
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Dec 6, 2009, 00:04 #41
- Join Date
- Apr 2003
- Location
- daejeon, South Korea
- Posts
- 2,223
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
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.)
-
Dec 6, 2009, 01:08 #42
- Join Date
- Jul 2006
- Location
- Augusta, Georgia, United States
- Posts
- 4,194
- Mentioned
- 17 Post(s)
- Tagged
- 5 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.
-
Dec 6, 2009, 01:47 #43
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Dec 6, 2009, 17:40 #44
- Join Date
- Apr 2003
- Location
- daejeon, South Korea
- Posts
- 2,223
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
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
-
Dec 6, 2009, 18:11 #45
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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?
-
Dec 6, 2009, 18:24 #46
-
Dec 6, 2009, 18:32 #47
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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 ...
-
Dec 6, 2009, 18:50 #48
- 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
-
Dec 6, 2009, 19:20 #49
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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?
-
Dec 6, 2009, 19:50 #50
Bookmarks