Ordering by fields in addition to grouping in MYSQL- Can I use conditional statements in sql?

Question

I have a database with a single table right now, which contains the following fields:

name, Suite

This is my first hacked together implementation of a database to track tests, and I realize that the way I am storing them is not efficient, but as I said, this is my first round of doing this.

Some of the names belong to suites, and some do not.

Assume the data looks like this:

Name | Suite
ggg | NULL
aaa | NULL
ddd | a_suite
fff | a_suite
ccc | some_suite
eee | some_suite
bbb | NULL

ect..

If I issue the following command:

select distinct name,suite from webpagetest order by name,suite

Then I get results that look like this:

Name | Suite
aaa | NULL
bbb | NULL
ccc | some_suite
ddd | a_suite
eee | some_suite
fff | a_suite
ggg | NULL

However, what I'm trying to do is first order the results by suite, then if the suite field is not null, use the suite field to alphabetize them. If suite is null, then alphabetize them by the name. So, the results should look like this:

aaa | NULL
ddd | a_suite
fff | a_suite
bbb | NULL
ggg | NULL
ccc | some_suite
eee | some_suite

In which case it is organizing by the name column if nothing is present in the suite column, and if suite is not null, then it will use the suite value instead.

Hopefully this isn't too poorly written, and someone can understand what it is I'm asking here.

Thanks! Matthew

Answer 1

Use COALESCE. It returns the first no-null value of the given parameters.

SELECT name, suite 
FROM webpagetest 
ORDER BY COALESCE(suite,name)