6

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

suruchi
  • 3
  • 3
VishwaKumar
  • 3,433
  • 8
  • 44
  • 72

3 Answers3

5

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.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • +1 we had the same thought about the unpivot of the data. I like yours without the user variables. The one thing is you have to unpivot twice. – Taryn Jan 09 '13 at 11:24
  • @bluefeet: Yeah, same thought. My MySQL know how isn't good enough to use those variables. Your solution is probably better though, from a performance perspective. And thanks for the heads-up with the SQLFiddle schema ;-) – Lukas Eder Jan 09 '13 at 11:25
3

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 |
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • Hmm, your solution might not return the correct value, if the largest value appears twice: http://sqlfiddle.com/#!2/16139/1 – Lukas Eder Jan 09 '13 at 11:36
  • @LukasEder fixed, needed one more check to be sure that the previous value does not match the current value. – Taryn Jan 09 '13 at 11:41
  • Great! And I learned something about user variables. I wonder if they can be generally used for the simulation of SQL standard window functions, e.g. ranking functions – Lukas Eder Jan 09 '13 at 11:45
0
SELECT MAX(col) FROM table WHERE col NOT IN (SELECT MAX(col) FROM table);

This gives you the second largest integer value in a specific column.

EDIT: then just swap the rows with the columns before doing that. But if the columns are dynamic, it could be quite tricky.

The best/easiest way would be to use a client side language and not SQL directly for this specific operation. If not possible, check this: Transpose rows and columns without aggregate

Community
  • 1
  • 1
N3sh
  • 881
  • 8
  • 37