List:General Discussion« Previous MessageNext Message »
From:Eamon Daly Date:January 30 2004 2:44am
Subject:Re: SELECT statement w/ Min() & Group By
View as plain text  
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" <t.fiedler@stripped>
To: <mysql@stripped>
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
>
> tfiedler@stripped
> t.fiedler@stripped (alias)
> 305-361-4626
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/mysql?unsub=1
>

Thread
SELECT statement w/ Min() & Group ByTristan Fiedler30 Jan
Re: SELECT statement w/ Min() & Group ByEamon Daly30 Jan