Question 20095 in Mysql Question posted in
The official MySQL documentation can be found here.

how to get index of no zero column in mysql

enter image description hereI have this table in my database.

I want to get the index of column which value equal 1 use select.
If have many one in one row,I want to get first index.
For example,1 for the first row,2 for the fifth row,0 for the eighth row.
this is a sql for init this table.

CREATE TABLE `action` (
  `action_id` BIGINT NOT NULL AUTO_INCREMENT,
  `action_level_0` SMALLINT DEFAULT NULL COMMENT 'L0',
  `action_level_1` SMALLINT DEFAULT NULL COMMENT 'L1',
  `action_level_2` SMALLINT DEFAULT NULL COMMENT 'L2',
  `action_level_3` SMALLINT DEFAULT NULL COMMENT 'L3',
   PRIMARY KEY (`action_id`)
) ENGINE=INNODB AUTO_INCREMENT=2
INSERT INTO `action` 
(`action_level_0`,`action_level_1`,`action_level_2`,`action_level_3`)
VALUES
(1,0,0,0),
(0,1,0,0),
(0,0,1,0),
(0,0,0,1),
(0,0,0,1);

I want to get this reuslt

result
L0
L1
L2
L3
L3
Tags:

2

Answers


  1. 0

    You can do it using CASE WHEN clause :

    select case when action_level_0 = 1 then 'L0'
            when action_level_1 = 1 then 'L1'
            when action_level_2 = 1 then 'L2'
            when action_level_3 = 1 then 'L3' end as Result
from `action`;

    Demo here

    Login or Signup to reply.
  2. 0

    One way without normalising the table is to do something like

    select 
  case when `action_level_0` = 1 then 'L0'
       when `action_level_1` = 1 then 'L1'
       when `action_level_2` = 1 then 'L2'
       when `action_level_3` = 1 then 'L3'
       else 'N/A' end as result
from `action`

    See fiddle http://sqlfiddle.com/#!9/baa2f/10 but as pointed out it may be wise to create column as pointed out.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search