Hi all, I have a table with the following data:
+--------+-----------+----------------------------------+ | lpcval | smiles_id | crhash | +--------+-----------+----------------------------------+ | 0.81 | 996 | 0597b6f84e0feaf9596869284e6e0660 | | 0.86 | 996 | 53a88ef9f72a77eeb47da15969e6fbc0 | | 0.71 | 996 | 251f58e8485335b094f06352e65bb6a8 | | 0.8 | 996 | dd59144f1df0c54f299a2f9a5587042a | | 0.86 | 997 | bad8fe7edb74c3ed4495a4825750d34d | | 0.88 | 997 | aefd10198ff8db947c78e0aa0e1e349d | | 0.76 | 997 | ade6c8f5d4911091eed515ad75db070a | | 0.73 | 998 | e2b0623ad9b77b95d76c00fb76614c0e | | 0.7 | 998 | 2672157a6ec823d2170cfe4b38123079 | | 0.78 | 998 | 92746af8be0431c2fd0dda646a1827cf | | 0.77 | 998 | ac087a6b796057e29941a2d1358c3eb1 | | 0.79 | 998 | a631bb5f3d3ecacd9c206f6d9f0c8bee | +--------+-----------+----------------------------------+ What I want to be able to do is for each value of smiles_id to be able to select the row with the largest value of lpcval. This should leave me with a table like +--------+-----------+----------------------------------+ | lpcval | smiles_id | crhash | +--------+-----------+----------------------------------+ | 0.86 | 996 | 53a88ef9f72a77eeb47da15969e6fbc0 | | 0.88 | 997 | aefd10198ff8db947c78e0aa0e1e349d | | 0.79 | 998 | a631bb5f3d3ecacd9c206f6d9f0c8bee | +--------+-----------+----------------------------------+ My closest attempt is select max(lpcval) as lpcval, smiles_id, crhash from results group by smiles_id; but that gives me the "wrong" crhash. I guess my understanding of max is less than it should be :) Any pointers on were to look next would be greatly appreciated. Dermot -- whathaveibecome? mysweetestfriend everyoneiknow goesawayintheend youcouldhaveitall myempireofdirt iwillletyoudown iwillmakeyouhurt ificouldstartagain amillionmilesaway iwouldkeepmyself iwouldfindaway -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]