Transform 2 Select Simple to 1

Question

Navigation

#1 by (1 votes)

0

I have the following table PLAYLIST where I have the columns VIDEO and STATUS :

VIDEO | STATUS

The status column varies between 1 and 0 being 1 for new videos and 0 for videos that have already been watched.

I'm currently running two select :

SELECT * FROM playlist WHERE status='1';
if result > 0  { 
   echo ok
} else {
 SELECT * FROM playlist WHERE status='0'
}

Can you make this query using only 1 SELECT ? preferably without using php?

My goal is to prioritize videos that have not been viewed.

php sql

asked by anonymous 03.04.2018 / 21:16

1 answer

Working with binary data How to return the page in LARAVEL 5.4

score 1 · Accepted Answer

I believe this will solve:

SELECT * FROM playlist WHERE
CASE
    WHEN (SELECT COUNT(*) FROM playlist WHERE status = 1) = 0 
    THEN status = 0
    ELSE status = 1
END

If the count of playlist with status = 1 is 0 it does a search with status = 0 otherwise, it does a search with status = 1

But if you just want to prioritize you can use ORDER BY status DESC , so those with status 1 will come first