You'll need to create a temporary table and then join the
result:
CREATE TEMPORARY TABLE tmp
SELECT unique_est_id, min(evalue) AS evalue
FROM blast_hit_master_seq2_unique_est_swiss_prot
GROUP BY unique_est_id;
SELECT a.*
FROM blast_hit_master_seq2_unique_est_swiss_prot AS a, tmp
WHERE
a.unique_est_id = tmp.unique_est_id AND
a.evalue = tmp.evalue
Note that you'll get multiple rows per unique_est_id if two
rows have the same minimum value for evalue. To then sort by
the highest score, I think you'll need to use the same
technique with a second temporary table:
CREATE TEMPORARY TABLE tmp
SELECT unique_est_id, min(evalue) AS evalue
FROM blast_hit_master_seq2_unique_est_swiss_prot
GROUP BY unique_est_id;
CREATE TEMPORARY TABLE tmp2
SELECT a.unique_est_id, max(a.score) AS score
FROM blast_hit_master_seq2_unique_est_swiss_prot AS a, tmp
WHERE
a.unique_est_id = tmp.unique_est_id AND
a.evalue = tmp.evalue
GROUP BY unique_est_id;
SELECT a.*
FROM blast_hit_master_seq2_unique_est_swiss_prot AS a, tmp2
WHERE
a.unique_est_id = tmp2.unique_est_id AND
a.score = tmp2.score;
You can find this method of grouping under section 12.6 of
the MySQL Cookbook: Finding Rows Containing Per-Group
Minimum or Maximum Values.
____________________________________________________________
Eamon Daly
NextWave Media Group LLC
Tel: 1 773 975-1115
Fax: 1 773 913-0970
----- Original Message -----
From: "Tristan Fiedler" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, January 29, 2004 7:45 PM
Subject: SELECT statement w/ Min() & Group By
> 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]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
