HOW TO SURFACE A VALUE FOR MORE THAN ONE ROW IN MYSQL

john rob

Total Post:108

Points:756
Posted by  john rob
MYSQL 
 1146  View(s)
Ratings:
Rate this:
Hi Expert!

I know that I can surface a row in a query by using it in the ORDER BY like this :

SELECT IF(`category` IS NOT NULL,`category`,"Uncategorized") AS `category` FROM `table` ORDER BY `category`="Uncategorized" DESC which will make the first row always contain "Uncategorized", however I have multiple rows that contain it that I also want surfaced. Here are two sample sets of returned data:

What I'm getting:

Uncategorized
Science
Health
Uncategorized
Wellness
What I want:

Uncategorized
Uncategorized
Health
Science
Wellness

I have tried a number of other things including a CASE and also using a conditional IF. What am I doing wrong?

Thanks in advance! 
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: How to surface a value for more than one row in MySQL

    Hi John!


    You using ORDER BY clause is comparing with the column name category and not on the alias given on the column.

    Try as following

    SELECT IF(category IS NOT NULL,category,'Uncategorized') category
    FROM   `table` 
    ORDER  BY IF(category IS NOT NULL,category,'Uncategorized')='Uncategorized' DESC
    you can alternatively use COALESCE or IFNULL to make it shorter

    SELECT COALESCE(category, 'Uncategorized') category
    FROM   `table` 
    ORDER  BY COALESCE(category, 'Uncategorized') = 'Uncategorized' DESC, category

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!