MySQL Row Calculation

nasidagang — Tue, 28 Apr 2009 10:56:09 +0800

Dear Lowyat,

I have a problem that I googled for solution/tutorial but couldn't get any answers.

i have 1 table that look like this:

CODE

+----+-----+----+----+---+
| id | uid | a | b | c |
+----+-----+----+----+---+
| 1 | 2 | 15 | 13 | 8 |
| 2 | 2 | 14 | 15 | 3 |
| 3 | 2 | 10 | 8 | 8 |
| 4 | 3 | 11 | 9 | 7 |
| 5 | 4 | 16 | 17 | 6 |
+----+-----+----+----+---+
5 rows in set (0.00 sec)

then, i do some calculation for each row with this formula (b-c)=rs1 , (a-c)=rs2 and greatest(rs1, rs2)=rs3. The result is here;

CODE

+----+-----+----+----+---+-----+-----+-----+
| id | uid | a | b | c | rs1 | rs2 | RS3 |
+----+-----+----+----+---+-----+-----+-----+
| 1 | 2 | 15 | 13 | 8 | 5 | 7 | 7 |
| 2 | 2 | 14 | 15 | 3 | 12 | 11 | 12 |
| 3 | 2 | 10 | 8 | 8 | 0 | 2 | 2 |
| 4 | 3 | 11 | 9 | 7 | 2 | 4 | 4 |
| 5 | 4 | 16 | 17 | 6 | 11 | 10 | 11 |
+----+-----+----+----+---+-----+-----+-----+
5 rows in set (0.01 sec)

And then, here come my problem, my boss want me to get the "C" value from "C" column which has the highest value of "rs3" for each UID and then take the "C" value and then make is as another column that is rs4:

CODE

+----+-----+----+----+---+-----+-----+-----+-----+
| id | uid | a | b | c | rs1 | rs2 | RS3 | RS4 |
+----+-----+----+----+---+-----+-----+-----+-----+
| 1 | 2 | 15 | 13 | 8 | 5 | 7 | 7 | 3 |
| 2 | 2 | 14 | 15 | 3 | 12 | 11 | 12 | 3 |
| 3 | 2 | 10 | 8 | 8 | 0 | 2 | 2 | 3 |
| 4 | 3 | 11 | 9 | 7 | 2 | 4 | 4 | 7 |
| 5 | 4 | 16 | 17 | 6 | 11 | 10 | 11 | 6 |
+----+-----+----+----+---+-----+-----+-----+-----+
5 rows in set (0.00 sec)

can this be done? help me please..

Lowyat.NET: Latest topics by nasidagang

MySQL Row Calculation