SQL sort for name with favorites on top

Question

I have a list with sort filters. Based on the parameter, I want it to show the favorites with that parameter on top while keeping them sorted by name (if on the same value):

Example 1: Order from A-Z

1. Alissa(*)
2. Frank(*)
3. Jessica(*)
4. Anna
5. Bob
6. Jack
7. Zack

Example 2: Order from last invoice

1. Jessica(*) /2024
2. Alissa(*) /2023
3. Frank(*) /2021
4. Jack /2024
5. Zack /2024
6. Anna /2023
7. Bob /2023

I tried

    `ORDER BY 
             favorite ${sortDirection}, name ${sortDirection}`

also

`ORDER BY 
        favorite DESC,
        CASE WHEN favorite = 1 THEN ${orderBy} END ${sortDirection},
        CASE WHEN favorite = 1 THEN name END ASC,
        CASE WHEN favorite = 0 THEN ${orderBy} END ${sortDirection},
        CASE WHEN favorite = 0 THEN name END ASC
    `;

${sortDirection} can be ASC or DESC. But in this case the favorites are shown from true to false, and they don't get sorted by name from A to Z but they have the same order they have in the database rows.

Answer 1

For the first example, I think you want something like:

SELECT *
FROM yourTable
ORDER BY
    favorite DESC,   -- favorite = 1 sorts first, favorite = 0 second
    name;            -- then sort each sub group by name