CODEWITHSUNDEEP
sqlpostgresqlselectdistinct
Hamza Ince
Hamza Ince

Reputation: 684

Trouble with query select distinct order by case

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

Upvotes: 1

Views: 166

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

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.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311723

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

Upvotes: 1

Related Questions