Re: Select problem

[Eternal Designs, Inc]

Dermot Frost wrote:

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

 

How about if you try this two-step solution:

create temporary table my_Smiles select lpcval, smiles_id, crhash from results order by
smiles_id asc, lpcval desc; select * from my_Smiles Group by smiles_id;
The first SQL statement creates a temporary table ordering by smiles_id, 
then by lpcval. The second uses Group By to pick the first. The reason 
is that the specification says that in a single statement GROUP BY must 
precede the ORDER BY. To beat that we have to use 2 statements.

--

Peter K. Aganyo
Eternal Designs Inc.,
+1(617)344-8023 (fax & voicemail)