Jump to content

[SOLVED] query, order, group by issue.


Recommended Posts



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.





MySQL Server version: 5.0.77


Link to comment
Share on other sites

How about:


SELECT t1.* FROM yourTable AS t1


( 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;

Link to comment
Share on other sites



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?



Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This thread is more than a year old. Are you sure you have something important to add to it?

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.