quote:

---

Originally posted by eevans

Here is my ORDER BY statement...

ORDER BY
(CASE WHEN class_cde='sr' THEN 1
WHEN class_cde='jr' THEN 2
WHEN class_cde='so' THEN 3
WHEN class_cde='fr' THEN 4
END)

I am using it in conjunction with a SELECT DISTINCT statement.

When I execute the query, I receive the following error message...

"ORDER BY items must appear in the select list if SELECT DISTINCT is specified."

The class_code column is contained in the SELECT DISTINCT statement.

Any suggestions? Thanks.

---

SELECT DISTINCT CASE WHEN class_cde='sr' THEN 1
WHEN class_cde='jr' THEN 2
WHEN class_cde='so' THEN 3
WHEN class_cde='fr' THEN 4
END,
....

ORDER BY 
(CASE WHEN class_cde='sr' THEN 1
WHEN class_cde='jr' THEN 2
WHEN class_cde='so' THEN 3
WHEN class_cde='fr' THEN 4
END)

or you can use like this also

SELECT DISTINCT OrdField,..
FROM
(
SELECT CASE WHEN class_cde='sr' THEN 1
WHEN class_cde='jr' THEN 2
WHEN class_cde='so' THEN 3
WHEN class_cde='fr' THEN 4
END AS OrdField,..
...
)t
ORDER BY OrdField

quote:

---

Originally posted by eevans

visakh16,
Your solution works. However, I end up with an unnecessary column containing the ordering values (1,2,3,4) in place of the actual values (SR,JR,SO,FR). Is there any way to eliminate this extra column? Thanks.

---

SELECT A.*
FROM (
    SELECT DISTINCT class_cde, ...
    FROM ...
  ) AS A
ORDER BY
(CASE WHEN class_cde='sr' THEN 1
WHEN class_cde='jr' THEN 2
WHEN class_cde='so' THEN 3
WHEN class_cde='fr' THEN 4
END)

quote:

---

Originally posted by eevans

Thanks. I appreciate you help.

The query I'm writing has a lot more variables that requires me to use SELECT DISTINCT

---