How to use ORDER BY to sort by multiple sorting conditions

user_2843856 0
Viewed 73

I want to write a query which will sort the results based on multiple conditions.

Please refer to following dummy table

name domain
Apple gmail
.Apple2 yahoo
_Apple3 xyz
Apple4 zyx
Ball abc
.Ball2 efg
_Ball3 gmail
Ball4 yahoo
Bear xyz
Cat abc

I want to sort above table

1 - By Known domain yahoo followed by gmail then rest.

So I want to divide result rows in three parts.

1st section with rows having yahoo as domain then in 2nd section rows with gmail domain at last rest of the rows.

2 - Alphabetically and all special characters at the end.

Every section should be further sorted alphabetically on name field with special characters at the end.

Result should look like following table.

name domain
Ball4 yahoo
.Apple2 yahoo
Apple gmail
_Ball3 gmail
Apple4 zyx
Ball abc
Bear xyz
Cat abc
_Apple3 xyz
.Ball2 efg

I have managed to sort result alphabetically with special symbols at bottom with following query but I am not able to get the 1st part of sorting correct i.e sort by known domain.

SELECT * FROM table1
ORDER BY
    CASE WHEN name GLOB '[A-Za-z]*'
          THEN name
          ELSE '~' || name
          END COLLATE NOCASE
sql-order-by sqlite android sql java
3 Answers

Part 1 - To sort by specific values first you can do something like this

SELECT * FROM table1
ORDER BY CASE 
           WHEN domain = 'yahoo' then 0 
           WHEN domain = 'gmail' then 1
           ELSE 2
         END ASC

Part 2 - Then within that you want to sort by name, so add your existing name sorting to our ORDER BY so we have this:

SELECT * FROM table1
ORDER BY CASE 
           WHEN domain = 'yahoo' then 0 
           WHEN domain = 'gmail' then 1
           ELSE 2
         END ASC,
 CASE WHEN name GLOB '[A-Za-z]*'
          THEN name
          ELSE '~' || name
          END COLLATE NOCASE
user_27447230

Use boolean expressions in the ORDER BY clause:

SELECT * FROM table1
ORDER BY domain = 'yahoo' DESC,
         domain = 'gmail' DESC,
         name GLOB '[A-Za-z]*' DESC,
         LTRIM(LTRIM(name, '.'), '_');

Each of these boolean expressions evaluates to 1 for true or 0 for false.

See the demo.

user_104988280

May not the most performant solution, but it should work with a union in such special cases:

(
  SELECT
    *
  FROM
    table1
  WHERE
    domain like 'yahoo'
  ORDER BY
    name desc
)
UNION
  (
    SELECT
      *
    FROM
      table1
    WHERE
      domain like 'gmail'
    ORDER BY
      name desc
  )
UNION
  (
    SELECT
      *
    FROM
      table1
    WHERE
      domain not like 'yahoo'
      OR domain not like 'gmail'
    ORDER BY
      domain desc,
      name desc
  )
user_192828660
Related