Custom Sort Order Using Distinct & Case Clauses

Hi I'm having difficulty in using the order by case statement when using alongside distinct keyword. Following is the table

Table with Data

Now in order to achieve custom sort order I'm using the following query

SELECT  state_name
FROM state_master
GROUP by state_name
order by
case 
    WHEN state_name = 'Goa' THEN 1
    WHEN state_name = 'Puducherry' THEN 2
    ELSE 3
END,
CASE 
        WHEN state_name NOT IN ('Goa', 'Puducherry') THEN state_name
        ELSE NULL
    END;

This yields the correctly sorted output of

Correctly Sorted Output

Now If I want to make sure that the values selected are distinct as well ( no duplicate records )

Then I'm facing error in sql query

SELECT  DISTINCT state_name
FROM state_master
GROUP by state_name
order by
case 
    WHEN state_name = 'Goa' THEN 1
    WHEN state_name = 'Puducherry' THEN 2
    ELSE 3
END,
CASE 
        WHEN state_name NOT IN ('Goa', 'Puducherry') THEN state_name
        ELSE NULL 
    END;

The error i'm getting

Error Mesaage

Where am I going wrong?
How can I fix it when distinct is applied to single column?
How can I fix this query if i want to apply distinct on multiple columns?

Thanks.

Everything I tried I have listed above, unable to figure this issue out.

Solution

I think the problem has nothing to do with your sorting:

Now If I want to make sure that the values selected are distinct as well ( no duplicate records )

Your GROUP BY is already making sure there are no duplicate values. This makes the DISTINCT redundant, and removing it will eliminate the error. The same will hold true if you GROUP BY multiple columns. Using DISTINCT instead of GROUP BY will potentially introduce issues when trying to ORDER BY.

But you can also consider using a CTE or a subquery to distinct de-duplicating from sorting altogether:

;WITH dedupe AS
(
  SELECT DISTINCT state_name /* , other columns */
    FROM state_master
)
SELECT state_name /* , other columns */
  FROM dedupe
 ORDER BY /* have a blast here */;