get 1st and 2nd highest vlaue rows in case of similar values

Question

I have a table with the columns : id, status, value.

id    status    value
--    ------    -----
1       10       100
2       10       100
3       10        60
4       11        20
5       11        15
6       12       100
7       12        50
8       12        50  

I would like to get the id and value of the first and second highest valued rows, from each status group. My table should have the following columns:
status, id of the first highest value, first highest value, id of second highest value, second highest value.

I should get:

status  1stID  1stValue  2ndID  2ndValue
------  -----  --------  -----  --------
  10     1/2      100     2/1      100
  11      4       20       5       15
  12      6      100      7/8      50   

I tried all kinds of solutions, but I couldn't find a solution for same-value 1st s (two rows with the same value, which happened to be the highest in that status group) or same-value seconds.

For example, in case of two rows sharing the highest value in their status group, this not-so-elegant query will return two rows with the same status, different 1sts and same 2nd:

    SELECT 2nds.status, 1sts.id AS "1stID",1sts.value AS "1stValue",
 2nds.id AS "2ndID",2nds.value AS "2ndValue" 
FROM    
    (SELECT v.* FROM 
    (SELECT status, MAX(value) AS "SecMaxValue" FROM table o
    WHERE value  < (SELECT MAX(value) FROM table 
                   WHERE status = o.status
                   GROUP BY status) AS m
    INNER JOIN table v
    ON v.status = m.status AND v.value = m.SecMaxValue) AS 2nds

    INNER JOIN

    (SELECT v.* FROM 
    (SELECT status, MAX(value) AS maxValue FROM table
    GROUP BY status) AS m
    INNER JOIN table v
    ON v.status = m.status AND v.value = m.MaxValue) AS 1sts    
    ON 1sts.status = 2nds.status ;

This query will give me:

status  1stID  1stValue  2ndID  2ndValue
------  -----  --------  -----  --------
  10      1      100       3       60
  10      2      100       3       60
  11      4       20       5       15
  12      6      100       7       50
  12      6      100       8       50     

To conclude, I would like to find a solution in which: a. if there are two rows with the highest value the query puts the details one of them in the column of the 1st and the details of other in 2nd (no mather which) b. if there are two rows with the second highst value it puts the highest in its place and one of the seconds in the second place.

Is there a way to change the query above? someone has a nicer solution?

• I came across several 1st and 2nd queries but they had the same problem - for example this solution: Finding the highest n values of each group in MySQL. it does not deliver 1st and 2nd in the same row, but the main problem it provides only one of the firsts.

Thanks

Answer 1

After spent a lot of time, finally I found a solution for above problem. Please try it out:

    select 1st.status as Status,
       SUBSTRING_INDEX(1st.id,'/',1) as 1stID,
       1st.value as 1stValue,
       (case when locate('/',1st.id) > 0 then SUBSTRING_INDEX(1st.id,'/',-1)
             else 2nd.id 
        end) as 2ndID,
        (case when locate('/',1st.id) > 0 then 1st.value
             else 2nd.value 
        end) as 2ndValue
        from
(
(select status, SUBSTRING_INDEX(Group_concat(id separator '/'),'/',2) as id,value
from t1
where (status,value) in (select status,value
from t1
group by status
having max(value))
group by status) 1st

inner join

(select status,id,value
from t1
where (status,value) not in (select status,value
from t1
group by status
having max(value))
group by status,value
order by status,value desc) 2nd
on 1st.status = 2nd.status)
group by 1st.status;

Just replace t1 with your tablename and it should work like a charm.

Click here for Updated Demo

If you have any doubt(s), feel free to ask.

Hope it helps!