Select distinct from multiple fields using sql

Question

I have 5 columns corresponding to answers in a trivia game database - right, wrong1, wrong2, wrong3, wrong4

I want to return all possible answers without duplicates. I was hoping to accomplish this without using a temp table. Is it possible to use something similar to this?:

select c1, c2, count(*)
from t
group by c1, c2

But this returns 3 columns. I would like one column of distinct answers.

Thanks for your time

Answer 1

This should give you all distinct values from the table. I presume you'd want to add where clauses to select only for a particular question. However, this solution requires 5 subqueries and can be slow if your table is huge.

SELECT DISTINCT(ans) FROM (
    SELECT right AS ans FROM answers
    UNION
    SELECT wrong1 AS ans FROM answers
    UNION
    SELECT wrong2 AS ans FROM answers
    UNION
    SELECT wrong3 AS ans FROM answers
    UNION
    SELECT wrong4 AS ans FROM answers
) AS Temp