Home > DeveloperSection > Forums > How to surface a value for more than one row in MySQL
john rob

Total Post:108

Points:756
Posted on    May-18-2013 1:23 AM

 MYSQL MYSQL 
Ratings:


 1 Reply(s)
 865  View(s)
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! 


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    May-18-2013 8:29 AM

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


Don't want to miss updates? Please click the below button!

Follow MindStick