CODEWITHSUNDEEP
mysqlsql
kishram
kishram

Reputation: 103

how to get second least column in a row in mysql

I have some 8 columns in a table.All the columns are of same type integer. 

|id  | marks_1 | marks_2 | marks_3|....|marks_7|
|1001|0        |20       |15      |....|25     |
|1002|20       |0        |11      |....|0      |
.
.
.
.
|1010|40       |0        |0       |....|25     |

I want to get the output as 

|id  |marks   |
|1001|15      |
|1002|11      |

I tried getting the least of these columns using the LEAST() function of MySql.

SELECT id, LEAST(marks_1,marks_2,marks_3,marks_4,marks_5,marks_6,marks_7) AS marks from tbl_name

and it is working.

It fetches me

|id  |marks   |
|1001|0       |
|1002|0       |

but I need the second least. i.e non-zero record from the row.

Upvotes: 2

Views: 256

Answers (4)

Danilo Bustos
Danilo Bustos

Reputation: 1093

try this: 

 SELECT t1.id, IFNULL(LEAST(COALESCE(NULLIF(t1.col_1, 0),NULLIF(t1.col_2, 0),NULLIF(t1.col_3, 0),NULLIF(t1.col_4, 0),NULLIF(t1.col_5, 0),NULLIF(t1.col_6, 0),NULLIF(t1.col_7, 0)),
         COALESCE(NULLIF(t1.col_2, 0),NULLIF(t1.col_3, 0),NULLIF(t1.col_4, 0),NULLIF(t1.col_5, 0),NULLIF(t1.col_6, 0),NULLIF(t1.col_7, 0),NULLIF(t1.col_1, 0)),
         COALESCE(NULLIF(t1.col_3, 0),NULLIF(t1.col_4, 0),NULLIF(t1.col_5, 0),NULLIF(t1.col_6, 0),NULLIF(t1.col_7, 0),NULLIF(t1.col_1, 0),NULLIF(t1.col_2, 0)),
         COALESCE(NULLIF(t1.col_4, 0),NULLIF(t1.col_5, 0),NULLIF(t1.col_6, 0),NULLIF(t1.col_7, 0),NULLIF(t1.col_1, 0),NULLIF(t1.col_2, 0),NULLIF(t1.col_3, 0)),
         COALESCE(NULLIF(t1.col_5, 0),NULLIF(t1.col_6, 0),NULLIF(t1.col_7, 0),NULLIF(t1.col_1, 0),NULLIF(t1.col_2, 0),NULLIF(t1.col_3, 0),NULLIF(t1.col_4, 0)),
         COALESCE(NULLIF(t1.col_6, 0),NULLIF(t1.col_7, 0),NULLIF(t1.col_1, 0),NULLIF(t1.col_2, 0),NULLIF(t1.col_3, 0),NULLIF(t1.col_4, 0),NULLIF(t1.col_5, 0)),
         COALESCE(NULLIF(t1.col_7, 0),NULLIF(t1.col_1, 0),NULLIF(t1.col_2, 0),NULLIF(t1.col_3, 0),NULLIF(t1.col_4, 0),NULLIF(t1.col_5, 0),NULLIF(t1.col_6, 0))),0) AS marks
       from tbl_name t1

Upvotes: 2

Bernd Buffen
Bernd Buffen

Reputation: 15057

this is not nice, but also works:

SELECT
    id 
    , SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(col1 ORDER BY col1), ',', -3),',',1)
FROM (
    SELECT id, col1 FROM test_table
    UNION SELECT id, col2 FROM test_table
    UNION SELECT id, col3 FROM test_table
    UNION SELECT id, col4 FROM test_table
    UNION SELECT id, col5 FROM test_table
) AS t
GROUP BY t.id
ORDER BY t.col1
;

sample

mysql> SELECT
    ->     id
    ->     , SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(col1 ORDER BY col1), ',', -3),',',1)
    -> FROM (
    ->     SELECT id, col1 FROM test_table
    ->     UNION SELECT id, col2 FROM test_table
    ->     UNION SELECT id, col3 FROM test_table
    ->     UNION SELECT id, col4 FROM test_table
    ->     UNION SELECT id, col5 FROM test_table
    -> ) AS t
    -> GROUP BY t.id
    -> ORDER BY t.col1
    -> ;
+----+-----------------------------------------------------------------------------------+
| id | SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(col1 ORDER BY col1), ',', -3),',',1) |
+----+-----------------------------------------------------------------------------------+
|  1 | 3                                                                                 |
|  2 | 6                                                                                 |
+----+-----------------------------------------------------------------------------------+
2 rows in set (0,00 sec)

mysql> select * from test_table;
+----+------+------+------+------+------+
| id | col1 | col2 | col3 | col4 | col5 |
+----+------+------+------+------+------+
|  1 |    1 |    3 |    2 |    5 |    4 |
|  2 |    7 |    8 |    6 |    3 |    2 |
+----+------+------+------+------+------+
2 rows in set (0,00 sec)

mysql>

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

If you what you want is the minimum number that is greater than 0, then you can unpivot your table and apply grouping to it:

SELECT id, MIN(c)
FROM (
   SELECT id, 
          CASE t2.n
             WHEN 1 THEN col_1
             WHEN 2 THEN col_2
             WHEN 3 THEN col_3
             WHEN 4 THEN col_4
             WHEN 5 THEN col_5
             WHEN 6 THEN col_6
             WHEN 7 THEN col_7
          END AS c
   FROM mytable AS t1
   CROSS JOIN (
      SELECT 1 AS n UNION ALL
      SELECT 2 UNION ALL
      SELECT 3 UNION ALL
      SELECT 4 UNION ALL
      SELECT 5 UNION ALL
      SELECT 6 UNION ALL
     SELECT 7) AS t2) AS x
WHERE x.c > 0   
GROUP BY id

Demo here

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269673

You can do this by normalizing the data and using variables:

select t.*
from (select t.*,
             (@rn := if(@id = id, @rn + 1,
                        if(@id := id, 1, 1)
                       )
             ) as rn
      from ((select id, col_1 as col, 'col_1' as colname from t) union all
            (select id, col_2 as col, 'col_2' as colname from t) union all
            . . .
           ) t cross join
           (select @rn := 0, @id := -1) params
      order by id, col asc
     ) t
where rn = 2;

Note: This version also gives the column name. You need to fill in the . . . with all the other columns.

Upvotes: 1

Related Questions