[SOLVED] PHP distinct with multiple rows/columns

[idire]

What I need to do is find the maximum value of a column for each distinct username

 

My previous query where all I was doing was looking for the highest values was:

 

SELECT * FROM `record` WHERE skill = '$skill' ORDER BY xp_gained DESC LIMIT 0 , 10

 

I was trying something like this for only one record per person:

 

SELECT DISTINCT (username), xp_gained, end_lvl, date FROM `record` WHERE skill = '$skill' ORDER BY xp_gained DESC LIMIT 0 , 10

 

This doesnt work, how would I retrieve the highest record for each user (but only 1 per user)

[DarkWater]

Check out GROUP BY.

[idire]

I've read 8 pages on google on distinct / group by and cant make it work

 

I tried:

 

SELECT * FROM record WHERE skill = '$skill' GROUP BY username ORDER BY xp_gained DESC LIMIT 0, 10

 

doesnt give me the highest value of $skill for each user

[idire]

oh looks like this works:

 

SELECT max(xp_gained), username, end_level, date FROM record WHERE skill = '$skill' GROUP BY username ORDER BY xp_gained DESC LIMIT 0, 10

 

Thanks

[idire]

correction it doesnt work

 

it retrieves the results, but doesnt put them in order of highest xp_gained

 

EDIT fixed it again using:

 

SELECT username, max(xp_gained) AS xp_gained, end_level, date FROM record WHERE skill = '$skill' GROUP BY username ORDER BY xp_gained DESC LIMIT 0, 10