SQL Case - how to order by another field ASC-CodePudding

I have a SQL query that sorts a table for a local restaurant into categories, but after that I would like to sort alphabetically by title. The end result should sort the categories, and then the content is sorted alphabetically by the title field (like "ORDER BY category, title ASC", only the category gets sorted by case).

My query is

SELECT *
FROM menu
ORDER BY CASE WHEN category = 'Appetizers' THEN 1
              WHEN category = 'Entrees' THEN 2
              WHEN category = 'Desserts' THEN 3
          WHEN category = 'Beverages' THEN 4
          END

I tried adding ", title ASC" to each line, and after the END but it came up as an error. Can this be done at all?

CodePudding user response：

It behaves like any other ORDER sorting

first it will sort by the CASE and then every entry with the same category by title

SELECT *
FROM menu
ORDER BY CASE WHEN category = 'Appetizers' THEN 1
              WHEN category = 'Entrees' THEN 2
              WHEN category = 'Desserts' THEN 3
          WHEN category = 'Beverages' THEN 4
          ELSE 5
          END ASC, title ASC

CodePudding user response：

Just need to add title in the ORDER BY clause after the CASE expression.This will sort the data by category based on the CASE and then the title within each category group.

Query

SELECT *
FROM menu
ORDER BY CASE WHEN category = 'Appetizers' THEN 1
          WHEN category = 'Entrees' THEN 2
          WHEN category = 'Desserts' THEN 3
      WHEN category = 'Beverages' THEN 4
      END, title;