CODEWITHSUNDEEP
mysql
Kinshuk Lahiri
Kinshuk Lahiri

Reputation: 1500

Mysql Get First null row among non nulls

I have this table structure:

quiz_id   id    end_utc
 40       40    2018-09-15 23:59:59
 41       41    2018-09-15 23:59:59
 42       42    2018-09-15 23:59:59
NULL      43    2018-09-15 23:59:59
NULL      45    2018-09-15 23:59:59

I want to get the first null row's id, in this case, 43, the query I currently have is this:

select DISTINCT answers.quiz_id as quiz_id, 
         id, 
        end_utc FROM quiz 
LEFT JOIN answers ON quiz.id = answers.quiz_id AND answers.is_active = 
1 AND answers.player_id = 1 WHERE contest_id = 2

If I try, AND quiz_id IS NULL, then it returns both 43 and 45, however, the first 3 rows are missing.

Any help is appreciated. Also, got something Top 1 * for SQL server, but doesn't work in MySQL.

Thanks!

PS:

Can we have expected output like this :

 quiz_id      id    end_utc
 40       40    2018-09-15 23:59:59
 41       41    2018-09-15 23:59:59
 42       42    2018-09-15 23:59:59
NULL      43    2018-09-15 23:59:59

Upvotes: 0

Views: 37

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Use Union to get results for two cases separately.

Try the following:

(
select DISTINCT answers.quiz_id as quiz_id, 
                id, 
                end_utc 
FROM quiz 
INNER JOIN answers ON quiz.id = answers.quiz_id 
                     AND answers.is_active = 1 
                     AND answers.player_id = 1 
WHERE contest_id = 2 
ORDER BY answers.id ASC 
)

UNION ALL 

(
select DISTINCT answers.quiz_id as quiz_id, 
                id, 
                end_utc 
FROM quiz 
LEFT JOIN answers ON quiz.id = answers.quiz_id 
                     AND answers.is_active = 1 
                     AND answers.player_id = 1 
WHERE contest_id = 2 
  AND answers.quiz_id IS NULL 
ORDER BY answers.id ASC LIMIT 1
)

ORDER BY id ASC

Upvotes: 1

Related Questions