sql - 查询有问题选择不同的顺序

Question

在问题的帮助下，我学会了如何将 SELECT DISTINCT 与 ORDER BY 一起使用:
How to use DISTINCT and ORDER BY in same SELECT statement?

我从这个问题中学会了使用ORDER BY CASE:
How do I return rows with a specific value first?

但是我有一个很难写的查询:

我有一个表 CUSTOMER，其中有一列 NAME，我想获取客户的所有 distinct 名称，我想找到名称 '首先是哈姆扎。

SELECT DISTINCT "CUSTOMER"."NAME",
    Max("CUSTOMER"."NAME")
FROM "CUSTOMER"
WHERE
    "CUSTOMER".id >= 2
GROUP BY "CUSTOMER"."NAME"
ORDER BY CASE WHEN ("CUSTOMER"."NAME" = 'HAMZA' ) THEN 1 ELSE 2 END

错误:

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 15: ORDER BY CASE WHEN ("CUSTOMER"."NAME" = 'HAMZA' ) THEN 1 EL...
_________________.^
SQL state: 42P10
Character: 372

---

编辑

解决方法:

SELECT "CUSTOMER"."NAME", -- distinct keyword removed here
    Max("CUSTOMER"."NAME")
FROM "CUSTOMER"
WHERE
    "CUSTOMER".id >= 2
GROUP BY "CUSTOMER"."NAME"
ORDER BY
CASE WHEN ("CUSTOMER"."NAME" = 'HAMZA' ) THEN 1 ELSE 2 END,
CASE WHEN ("CUSTOMER"."NAME" = 'GIORNO' ) THEN 1 ELSE 2 END

Answer 1

分组将确保您获得不同的客户名称。只需删除此修饰符，您应该没问题:

SELECT "CUSTOMER"."NAME", -- distinct keyword removed here
    Max("CUSTOMER"."NAME")
FROM "CUSTOMER"
WHERE
    "CUSTOMER".id >= 2
GROUP BY "CUSTOMER"."NAME"
ORDER BY CASE WHEN ("CUSTOMER"."NAME" = 'HAMZA' ) THEN 1 ELSE 2 END