skip to Main Content

I have following data in tourament MySQL table.

id name team
1 Warren A
2 Carol B
3 Donna
4 William A
5 Andrew C
6 Neil A
7 Max A
8 Phil
9 William
10 Keith
11 Phil

I am trying to create a query to output the data in the following form to process it further.

name team
A 1
B 1
C 1
Donna 0
Keith 0
Phil 0
Phil 0
William 0

Please have a look here for data.

Current attempt:

select COALESCE(NULLIF(`team`,''), `name`) as name, 
case
    when `team` = '' THEN false
    when `team` != '' THEN true
end as `group`
from `tournament` order by COALESCE(NULLIF(`team`, ''), `name`) ASC;

3

Answers


  1. Your attempt is good, you just need to rewrite COALESCE(NULLIF(team,''), name) to COALESCE(team, name) because COALESCE will only move on to the next parameter if the current parameter is NULL. Since'' != NULL the name column will never be selected.

    Something like:

    SELECT COALESCE(team, name) as name, 
       CASE when team IS NULL THEN 0 ELSE 1 END as group 
    FROM tournament 
    ORDER BY COALESCE(team, name);
    
    Login or Signup to reply.
  2. Your approach is good!
    Try the below code to achieve the expected output:

    SELECT 
        team AS name, 
        case
            when `team` = '' THEN false
            when `team` != '' THEN true
        end as `group` 
    FROM tournament 
    WHERE team IS NOT NULL AND team != '' 
    GROUP BY team 
    UNION ALL 
    SELECT name, 0 AS team 
    FROM tournament 
    WHERE team IS NULL OR team = ''
    ORDER BY name;
    
    Login or Signup to reply.
  3. It works also without union like this

    SELECT 
        MAX(name) AS name,
        IF(team = '',0,1) as team
    FROM tournament 
    GROUP BY team,name
    ORDER BY IF(team = '',1,0),name ASC;
    

    result:

    name    team
    Andrew  1
    Carol   1
    Max     1
    Neil    1
    Warren  1
    William 1
    Donna   0
    Keith   0
    Phil    0
    William 0
    

    click here for a sample

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search