1

I've learned how to use SELECT DISTINCT with ORDER BY with the help of the question :
How to use DISTINCT and ORDER BY in same SELECT statement?

And I've learned to use ORDER BY CASE from this question:
How do I return rows with a specific value first?

But I have a query that is hard to write:

I have a table CUSTOMER with a column NAME, I want to get all the distinct names of the customers, and I want to find name 'Hamza' first.

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


The ERROR:

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

Edit

It's solved with:

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
Mureinik
  • 297,002
  • 52
  • 306
  • 350
Hamza Ince
  • 604
  • 17
  • 46

2 Answers2

1

The grouping will already ensure you get distinct customer names. Just drop this modifier and you should be fine:

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
Mureinik
  • 297,002
  • 52
  • 306
  • 350
1

I want to get all the distinct names of the customers, and I want to find name 'Hamza' first.

Why not just do this?

SELECT "CUSTOMER"."NAME"
FROM "CUSTOMER" 
GROUP BY "CUSTOMER"."NAME" 
ORDER BY ("CUSTOMER"."NAME"  = 'Hamza') DESC

Using SELECT DISTINCT with aggregation functions usually just confuses the query logic.

I also have no idea why you are selecting the name twice.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Indeed the solution was not using DISTINCT, but for the rest of the query, it may not make senses, because there was business things, I've changed – Hamza Ince Jul 12 '19 at 11:25