Hi all,
I have a table with the following data:
+--------+-----------+----------------------------------+
| lpcval | smiles_id | crhash |
+--------+-----------+----------------------------------+
| 0.81 | 996 | 0597b6f84e0feaf 9596869284e6e06 60 |
| 0.86 | 996 | 53a88ef9f72a77e eb47da15969e6fb c0 |
| 0.71 | 996 | 251f58e8485335b 094f06352e65bb6 a8 |
| 0.8 | 996 | dd59144f1df0c54 f299a2f9a558704 2a |
| 0.86 | 997 | bad8fe7edb74c3e d4495a4825750d3 4d |
| 0.88 | 997 | aefd10198ff8db9 47c78e0aa0e1e34 9d |
| 0.76 | 997 | ade6c8f5d491109 1eed515ad75db07 0a |
| 0.73 | 998 | e2b0623ad9b77b9 5d76c00fb76614c 0e |
| 0.7 | 998 | 2672157a6ec823d 2170cfe4b381230 79 |
| 0.78 | 998 | 92746af8be0431c 2fd0dda646a1827 cf |
| 0.77 | 998 | ac087a6b796057e 29941a2d1358c3e b1 |
| 0.79 | 998 | a631bb5f3d3ecac d9c206f6d9f0c8b ee |
+--------+-----------+----------------------------------+
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 | 53a88ef9f72a77e eb47da15969e6fb c0 |
| 0.88 | 997 | aefd10198ff8db9 47c78e0aa0e1e34 9d |
| 0.79 | 998 | a631bb5f3d3ecac d9c206f6d9f0c8b ee |
+--------+-----------+----------------------------------+
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 ? mysweetestfrien d everyoneiknow goesawayintheen d
youcouldhaveita ll myempireofdirt iwillletyoudown iwillmakeyouhur t
ificouldstartag ain amillionmilesaw ay iwouldkeepmysel f iwouldfindaway
--
MySQL General Mailing List
For list archives:
http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/my***********...ie.nctu.edu.tw