How to use ORDER BY to sort by multiple sorting conditions-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

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
  )