[SOLVED] query, order, group by issue.

[ann]

Hi

 

Can some one tell me if this is possible in a single query and if so how would I go about it?

 

If I've got a table like...

experiment           probe         pvalue
       a               1           0.001
       a               2           0.7
       a               3           0.01
       b               1           0.7
       b               2           0.7
       b               3           0.0002
       c               1           0.7
       c               2           0.7
       c               3           0.0001

 

and I want results like...

experiment           probe         pvalue
       c               3           0.0001
       b               3           0.0002
       a               3           0.01
       a               1           0.001
       b               1           0.7
       c               1           0.7

 

I can do this in two steps by...

    SELECT distinct(probe) FROM TABLE WHERE pvalue<0.05 order by pvalue;

then using php to loop through the results ...

    SELECT * FROM TABLE WHERE probe='<each results from above query>' order by pvalue

but it's very slow.

 

I'll try and put it in words as 'I want all the rows for those probes which had a pvalue<0.05 in any experiment and I want the results grouped by probe and ordered by the lowest pvalue of each group of probes'.

 

I can't even think of a search term that might help me solve this so any hints would be appreciated.

 

Thanks

 

 

MySQL Server version: 5.0.77

 

[fenway]

How about:

 

SELECT t1.* FROM yourTable AS t1

INNER JOIN

( SELECT experiment, probe FROM yourTable WHERE pvalue<0.05 GROUP BY experiment, probe )

t2 USING (experiment, probe )

order by t1.probe ASC, t1.pvalue ASC;

[ann]

Thanks, 

 

I think that gives me the probe 1 group first, but I need probe 3 first because that group contains the lowest pvalue in the whole data set.

 

If you imagine all these rows chucked in a pile I want to take from the pile the row with the lowest pvlaue,

then take all the other rows with the same probe id,

then from what's left take the lowest pvalue again, etc. 

 

If it's possible?

 

 

[Ken2k7]

Change t1.probe ASC to t1.probe DESC? Kind of obvious isn't it?

[ann]

I don't have the language to make the question obvious.  I need the rows grouped on probe number and the groups ordered by the lowest pvalue in each group. 

 

The lowest pvalue in the data set is

      c              3          0.0001

 

so I want all the probe 3's first.  Having used the probe 3's, the lowest pvalue in the set is now...

      a              1          0.001

 

so I want all the probe 1's next.  And if I'd used "WHERE pvalue<0.08" I'd get all the probe 2's next.

(Within group ordering on pvalue would be nice but it's secondary to getting the groups in the right order)

 

I found a page in the manual about Create Procedure or Function.  Maybe that's the way to go.  I'll go do some reading.

[fenway]

Right... that makes sense.  The trick is to "order" by the lowest probe value, and then carry that forwards.  Like this:

 

select t1.* 
from exp as t1
inner join
( 
   SELECT r1.*, @rank:=@rank+1 as rank
      FROM ( 
         SELECT probe, MIN(pvalue) AS pvalue FROM exp WHERE pvalue<0.05 GROUP BY probe ORDER BY pvalue ASC
      ) as r1
      cross join ( select @rank := 0 ) as r2
) as t2 using ( probe )
order by t2.rank ASC, t1.pvalue ASC

 

No SP required!

[ann]

Thanks Fenway, that's just what I'm looking for.