order by case alphabetical ordering

Question

Question is not too specific but not sure how to explain the question well. I have table in my db with names in the field. I would like to order the names in the way if the name starts with certain alphabet, order that first and so on. What I have now is

SELECT
  (T.firstname||' '||T.lastname) as Full_Name
FROM 
  TABLE T
ORDER BY
  CASE
    WHEN LPAD(T.firstname, 1) = 'J' THEN T.firstname
    WHEN LPAD(T.firstname, 1) = 'B' THEN T.firstname
  END DESC,
Full_Name ASC

Now this returns as what I would like to see, name starting with 'J' is ordered first then 'B' then the rest. However, the result looks like

What I get    What I want

Full_Name     Full_Name
----------    ----------
Junior MR     James A
John Doe      Joe Bob
Joe Bob       John Doe
James A       Junior MR
Brad T        B Test
Bob Joe       Bb Test
Bb Test       Bob Joe
B Test        Brad T
A Test        A Test
Aa Test       Aa Test
AFLKJASDFJ    AFLKJASDFJ
Ann Doe       Ann Doe

But what I want is that J and B to be sorted alphabetical order as well, right now it is doing reverse alphabetical order. How can I specify the order inside of case? I tried having 2 seperate case statement for different cases for starting with 'J' and 'B', it just shows me the same result

Answer 1

Just make one extra column, material using triggers or volatile using expression only executed when select is run, and then use it in sorting.

For secondary sorting use original components of names, not the expression bringing both names together thus destroying the information which was which.

Examples: https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=fbf89b3903d3271ae6c55589fd9cfe23

 create table T (
   firstname varchar(10),
   lastname varchar(10),
   fullname computed by ( 
       Coalesce(firstname, '-') || ' ' || Coalesce(T.lastname, '-')  
   ),
   sorting_helper computed by (
     CASE WHEN firstname starting with 'J' then 100
          WHEN firstname starting with 'B' then 50
          ELSE 0
     END
   )
 )

Notice the important distinction: my helper expression is "ranking" one. It yields one of several pre-defined ranks, thus putting "James" and "Joe" into the same bin having exactly the same ranking value. Your expression still yields the names themselves, thus erroneously keeping difference between those names. But you do NOT want that difference, you told you want all J-started names to be moved upwards and then sorted among themselves by usual rules. So, just do what you say, make an expression that pulls all J-names together WITHOUT making distinction between those.

 insert into T
   select 
     'John', 'Doe'  
   from rdb$database union all select
     'James', 'A'  
   from rdb$database union all select
     'Aa ', 'Test'  
   from rdb$database union all select
     'Ann', 'Doe'  
   from rdb$database union all select
     'Bob', 'Joe'  
   from rdb$database union all select
     'Brad', 'Test'  
   from rdb$database union all select
     NULL, 'Smith'  
   from rdb$database union all select
     'Ken', NULL  
   from rdb$database 

8 rows affected

 select * from T

FIRSTNAME | LASTNAME | FULLNAME    | SORTING_HELPER
:-------- | :------- | :---------- | -------------:
John      | Doe      | John  Doe   |            100
James     | A        | James A     |            100
Aa        | Test     | Aa    Test  |              0
Ann       | Doe      | Ann   Doe   |              0
Bob       | Joe      | Bob   Joe   |             50
Brad      | Test     | Brad  Test  |             50
null      | Smith    | - Smith     |              0
Ken       | null     | Ken   -     |              0

 Select FullName from T order by sorting_helper desc, firstname asc, lastname asc

| FULLNAME    |
| :---------- |
| James A     |
| John  Doe   |
| Bob   Joe   |
| Brad  Test  |
| - Smith     |
| Aa    Test  |
| Ann   Doe   |
| Ken   -     |

Or without computed-by column

 Select FullName from T order by (CASE WHEN firstname starting with 'J' then 0
          WHEN firstname starting with 'B' then 1
          ELSE 2
     END) asc, firstname asc, lastname asc

| FULLNAME    |
| :---------- |
| James A     |
| John  Doe   |
| Bob   Joe   |
| Brad  Test  |
| - Smith     |
| Aa    Test  |
| Ann   Doe   |
| Ken   -     |

For extra tuning of the positioning of the rows lacking name or surname you can also use NULLS FIRST or NULLS LAST option as described in Firebird docs at https://firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/nullguide-sorts.html

---

The problem with this approach however, on big enough tables, would be that you won't be able to use indices built over names and surnames for sorting, instead you would have to resort to un-sorted pulling of data (aka NATURAL SORT when reading QUERY PLAN) and then sorting it into temporary files on disk. Which might turn very slow and volume-consuming on large enough data.

You can try to make it better by creating "index by the expression", using your ranking expression there. And hope that FB optimizer will use it (it is quite tricky with verbose expressions like CASE). Frankly you would probably still be left without it (at least I did not manage to make FB 2.1 utilize index-by-case-expression there).

You can "materialize" the ranking expression into a regular SmallInt Not Null column instead of COMPUTED BY one, and use TRIGGER of BEFORE UPDATE OR INSERT type keep that column populated with proper data. Then you can create a regular index over that regular column. While it will add two bytes to each row, that is not that much a grow.

But even then, the index with very few distinct values does not add much value, it will have "low selectivity". Also, index-by-expression can not be compound one (meaning, including other columns past the expression).

So for large data you'd practically better be with using THREE different queries fused together. Add scaffolding, if you did not do already:

create index i58647579_names on T58647579 ( firstname, lastname )

Then you can do triple-select like this:

WITH S1 as (
  select FullName from T58647579
  where firstname starting with 'J' 
  order by firstname asc, lastname asc
), S2 as (
  select FullName from T58647579
  where firstname starting with 'B'
  order by firstname asc, lastname asc
), S3 as (
  select FullName from T58647579
  where (firstname is null)
     or (  (firstname not starting with 'J')
       and (firstname not starting with 'B')
        )
  order by firstname asc, lastname asc
)
SELECT * FROM S1
   UNION ALL
SELECT * FROM S2
   UNION ALL
SELECT * FROM S3

And while you would traverse the table thrice - you would do it by pre-sorted index:

PLAN (S1 T58647579 ORDER I58647579_NAMES INDEX (I58647579_NAMES))
PLAN (S2 T58647579 ORDER I58647579_NAMES INDEX (I58647579_NAMES))
PLAN (S3 T58647579 ORDER I58647579_NAMES)