Hi I'm having difficulty in using the order by case statement when using alongside distinct keyword. Following is the table
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
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
Thanks.
Everything I tried I have listed above, unable to figure this issue out.
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 */;