sqlsqlitebetter-sqlite3

SQL sort for name with favorites on top


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.


Solution

  • 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