SQL: Get at least the Third Largest Value from a set of columns of a row

Question

I need a single SQL query to get the second largest value from a set of columns of a row. For example, if these are my table's rows:

id | col1 | col2 | col3 | col4 | coln |
1  |   5  |   7  |   9  |  3   |  10  |
2  |   13 |   14 |   2  |  54  |  11  |

For rowid 1 - I need the value 9, rowid 2 - I need the value 14

Answer 1

I'm afraid that, without common table expressions and/or window functions and without resorting to writing a procedure, this gets horribly verbose in MySQL

SELECT t.id, t.val second_largest
-- unpivot your columns into a table
FROM (
  SELECT id, col1 val FROM my_table UNION ALL
  SELECT id, col2     FROM my_table UNION ALL
  SELECT id, col3     FROM my_table UNION ALL
  SELECT id, col4     FROM my_table UNION ALL
  SELECT id, coln     FROM my_table
) t

-- retain only those records, where there exists exactly one record with a
-- column value greater than any other column value with the same id
WHERE 1 = (
  SELECT COUNT(*) 
  -- Here, use unions to be sure that every value appears exactly once
  FROM (
    SELECT id, col1 val FROM my_table UNION
    SELECT id, col2     FROM my_table UNION
    SELECT id, col3     FROM my_table UNION
    SELECT id, col4     FROM my_table UNION
    SELECT id, coln     FROM my_table
  ) u
  WHERE t.id = u.id
  AND t.val < u.val
)

Here's the SQLFiddle to check it (thanks to bluefeet for the heads-up with the schema!). The above solution will find the second largest column value in every row, even if the largest column value appears more than once.

Answer 2

You could do this by unpivoting the data and then applying a row number to each record in the id group. The unpivot takes the data from the column layout and places it into rows so it is easier to determine the second highest value:

select id, col, value
from
(
  -- assign a group row number to each record 
  select *, 
    @row:=(case when @prev=id and @prevvalue<>value then @row else 0 end) + 1 as rownum,
    @prevvalue:=value,
    @prev:=id pid
  from
  (
    -- unpivot the multi columns into row values
    select id, 'col1' col, col1 value
    from yourtable
    union all
    select id, 'col2' col, col2 value
    from yourtable
    union all
    select id, 'col3' col, col3 value
    from yourtable
    union all
    select id, 'col4' col, col4 value
    from yourtable
    union all
    select id, 'coln' col, coln value
    from yourtable
  ) src
  order by id, value desc
) src
-- apply filter looking for the rownumber = 2 which is the second highest based on order
where rownum = 2

See SQL Fiddle with Demo

The result will show:

| ID |  COL | VALUE |
---------------------
|  1 | col3 |     9 |
|  2 | col2 |    14 |