SQlite query order by case

Question

I have a table containing 2 types of text inside a column. The first type is an email address string (ex [email protected]) and the second is a persons name (John Doe)

I am using this query to get the data sorted so that first the rows that don't have the @ char are shown and then the ones that do have it:

  SELECT * 
    FROM Name 
ORDER BY CASE 
           WHEN displayName LIKE '%@%' THEN 1 
           ELSE 2 
         END

So what I am unable to do is get the cases to be sorted ascending so that I have the names sorted by letter ascending following the emails sorted by letter ascending.

Answer 1

Use:

  SELECT n.* 
    FROM NAME n
ORDER BY CASE 
           WHEN displayName LIKE '%@%' THEN 1 
           ELSE 2 
         END, n.displayname COLLATE NOCASE

The ORDER BY clause supports more than one column, but the priority is read left to right. So the displayname values with an "@" in them are at the top, and then ordered by the displayname value in that each grouping (based on the CASE statement).

You need to look at using the COLLATE operator for case insensitive comparison.

Answer 2

Basically, it would look like this:

Select * 
FROM Name 
Order by case when displayName LIKE '%@%' then 1 else 2 end, displayName

You are just adding a second item to be sorted onto the list. This will sort first by the 1 or 2 column and then it will sort those results by the actual name.

If your collation doesn't allow for the sort you want, you could modify the ORDER BY slightly to accomodate this like so:

Select * 
FROM Name 
Order by case when displayName LIKE '%@%' then 1 else 2 end, UPPER(displayName)