Select problem

[Dermot Frost]

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/my***********...ie.nctu.edu.tw