Get second least column in a row in mysql, MySQL

Get second least column in a row in mysql

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

select version() as 'mysql version';

DROP TABLE IF EXISTS `mytable`;

CREATE TABLE mytable (id int, col_1 int, col_2 int, col_3 int,
                      col_4 int, col_5 int, col_6 int, col_7 int);
INSERT INTO mytable VALUES
(1001, 0, 20, 15, 12, 5, 8, 25),
(1002, 0, 30, 25, 12, 15, 18, 0);

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   ;

DROP TABLE mytable;


run \| edit \| history \| help	0