Greetings MySQL group: I am new to the group so if this is posted to the wrong place, please inform me.
Using the following table : +---------------+------------+-------+--------+ | unique_est_id | sp_id | score | evalue | +---------------+------------+-------+--------+ | 2 | RL24_CICAR | 100 | 2e-21 | | 2 | RL24_ARATH | 99 | 5e-21 | | 2 | RL24_KLULA | 99 | 5e-21 | | 2 | RL24_HUMAN | 171 | 6e-43 | | 2 | R24A_YEAST | 97 | 2e-20 | | 2 | R24B_YEAST | 96 | 3e-20 | | 6 | RS10_SULSO | 69 | 4e-12 | | 6 | RS10_SULTO | 69 | 6e-12 | | 6 | RS10_SULAC | 69 | 6e-12 | | 6 | RS10_BORBU | 63 | 2e-10 | | 6 | RS10_VIBPA | 58 | 8e-09 | | 6 | RS10_PYRAE | 72 | 4e-13 | | 6 | RS10_CAMJE | 57 | 2e-08 | | 6 | RS10_XANCP | 56 | 3e-08 | | 8 | MIP_DROME | 53 | 1e-06 | | 8 | SM34_LYTPI | 57 | 1e-07 | +---------------+------------+-------+--------+ I would like to : For each distinct unique_est_id, get the minimum e-value **and** the score and sp_id associated with that e-value. I assume something similar to : mysql> select unique_est_id, sp_id, score, min(evalue) from blast_hit_master_seq2_unique_est_swiss_prot group by unique_est_id ; The min(evalue) function properly returns the smallest e-value for each unique_est_id, however, how do I then select the sp_id and score associated with this evalue? Many thanks! -- Tristan J. Fiedler, Ph.D. Postdoctoral Research Fellow - Walsh Laboratory NIEHS Marine & Freshwater Biomedical Sciences Center Rosenstiel School of Marine & Atmospheric Sciences University of Miami [EMAIL PROTECTED] [EMAIL PROTECTED] (alias) 305-361-4626 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
