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
Example 2: Order from last invoice
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.
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