MySQL HIGHEST/LOWEST 5 from a ROW of data

I have the following query:

SELECT AVG(q1) AS q1, AVG (q2) AS q2, AVG(q3) AS q3, AVG(q4) AS q4, AVG(q5) AS q5 FROM tresults_acme

q1    q2    q3    q4    q5
8.23  5.23  7.65  8.00  7.99

This query brings back a single row of data, headed q1, q2, q3, q4, q5 - is it possible in MySQL to just show the Highest two开发者_如何学Go (q1 & q4) or Lowest two (q2 & q3) - if not in MySQL can it be done via PHP?

Thanks in advance,

Homer.

You could use a procedure, but here is a pure SQL way of doing it:

    select greatest(q1,q2,q3,q4,q5) as highest_1,
           least( greatest(q1,q2,q3,q4),
                  greatest(q1,q2,q3,q5),
                  greatest(q1,q2,q4,q5),
                  greatest(q1,q3,q4,q5),
                  greatest(q2,q3,q4,q5) ) as highest_2
    from ( SELECT AVG(q1) AS q1, 
                  AVG(q2) AS q2, 
                  AVG(q3) AS q3, 
                  AVG(q4) AS q4, 
                  AVG(q5) AS q5 
           FROM tresults_acme ) t;

tested with:

create view tresults_acme as select 1 as q1, 2 as q2, 3 as q3, 4 as q4, 5 as q5;

result:

         highest_1      |     highest_2
    --------------------+--------------------
     5.0000000000000000 | 4.0000000000000000