[SOLVED] MySQL: Max change between two dates and multiple usernames

[idire]

I have a table with the following rows:

 

date

username

xp

 

I have two dates, today and yesterday

 

What I want to locate is the username with the max xp change from the row today and the row yesterday so I can then retrieve via

 

while($r = mysql_fetch_array($sql))

 

the usernames in order of heighest exp change first.

 

Any ideas?

 

 

---

Thanks

 

[idire]

i can retrieve the max of a value if i already have the value stored in the table, but not like this

[sasa]

try

SELECT t.name, t.xp-y.xp as change FROM table_name as y, table_name as t WHERE y.`date`=yesterday_date and t.`date`=today_date and y.name=t.name ORDER BY change DESC

[idire]

Thanks for the advice, I tried this:

 

<?php
$sql = "SELECT t.username, t.overall_xp-y.overall_xp as change FROM `user` as y, `user` as t WHERE y.`date`='$yesterday' and t.`date`='$today' and y.username=t.username ORDER BY change DESC";
echo $sql;
$result = mysql_query($sql);

while($r = mysql_fetch_array($result_today)) {
echo $r["t.username"];
}
?>

 

<?php
SELECT t.username, t.overall_xp-y.overall_xp as change FROM `user` as y, `user` as t WHERE y.`date`='2008-09-12' and t.`date`='2008-09-13' and y.username=t.username ORDER BY change DESC

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/content/i/d/i/idire/html/test2.php on line 10
?>

[idire]

Error

 

SQL query: Documentation

 

SELECT t.username, t.overall_xp - y.overall_xp AS

CHANGE FROM `user` AS y, `user` AS t

WHERE y.`date` = '2008-09-12'

AND t.`date` = '2008-09-13'

AND y.username = t.username

ORDER BY CHANGE DESC

LIMIT 0 , 30

 

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'change FROM `user` as y , `user` as t WHERE y . `date` = '2008-09-12' and t . `d' at line 1

[Adam]

well i sort of get you, but what date will be stored in date? yesturday, today?

 

could try something like..

 

SELECT max(xpField) FROM tableName WHERE dateField = 'someDate'

 

Never really used max before though, mentions a lot about grouping the results, could try using it...

[idire]

i have data stored from today AND yesterday for LOTS of users

 

What i want to do is find a list of users ordered in te max change between the data stored yesterday and the data stored from today

[DarkWater]

<?php
$sql = "SELECT t.username, t.overall_xp-y.overall_xp as exp_change FROM `user` as y, `user` as t WHERE y.`date`='$yesterday' and t.`date`='$today' and y.username=t.username ORDER BY exp_change DESC";

[idire]

<?php
SELECT t.username, t.overall_xp-y.overall_xp as exp_change FROM `user` as y, `user` as t WHERE y.`date`='2008-09-12' and t.`date`='2008-09-13' and y.username=t.username ORDER BY exp_change DESC
Unknown column 't.overall_xp' in 'field list'
?>

[DarkWater]

Okay, is overall_xp the right column name? =/

[idire]

Ah, i was using the wrong table, needed the tracking table, not the user table, as the field overall_xp is in the tracking table.

 

This seems to work, many thanks

 

If I wanted distinct user, I would need to change it to:

 

SELECT distinct(t.username), t.overall_xp-y.overall_xp as exp_change FROM `user` as y, `user` as t WHERE y.`date`='2008-09-12' and t.`date`='2008-09-13' and y.username=t.username ORDER BY exp_change DESC

 

I dont think I need to change that,  but thats how it would work?

 

 

EDIT: Its now working in my live page, thanks