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
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
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
The DISTINCT is superfluous, because the UNION will not return rows that are identical for all columns. (When you want duplicated, or if you know that no duplicates exist, use UNION ALL for faster performance)
This will give you a single list containing all the answers. You'll still have duplicates though if you have multiple copies of the same answer within a single column.
That should not be the case if you use UNION, only if you use UNION ALL
SELECT [value] INTO #TEMP
FROM
(
SELECT [value] = 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 1
) AS X
(4 row(s) affected)
SELECT [value]
FROM #TEMP
value
-----------
1
2
3
1
(4 row(s) affected)
SELECT [value]
FROM #TEMP
UNION
SELECT [value]
FROM #TEMP
value
-----------
1
2
3
(3 row(s) affected)
I provided one answer above.
However I figured a much better way to do it using UNPIVOT.
SELECT DISTINCT(ans)
FROM (
SELECT [Name], ANS
FROM (
SELECT right, wrong1, wrong2, wrong3, wrong4
FROM answers
) AS PVT
UNPIVOT
(ans FOR [Name] IN (right, wrong1, wrong2, wrong3, wrong4)) AS UNPVT
) AS OUTPUT;
You can provide any WHERE clause in the internal subquery:
SELECT DISTINCT(ans)
FROM (
SELECT [Name], ANS
FROM (
SELECT right, wrong1, wrong2, wrong3, wrong4
FROM answers
WHERE (...)
) AS PVT
UNPIVOT
(ans FOR [Name] IN (right, wrong1, wrong2, wrong3, wrong4)) AS UNPVT
) AS OUTPUT;
Well you can use a UNION and run 5 select statements, one for each column in your table. It would look something like this:
SELECT right FROM answers
UNION
SELECT wrong1 FROM answers
UNION
SELECT wrong2 FROM answers
UNION
SELECT wrong3 FROM answers
UNION
SELECT wrong4 FROM answers
This will give you a single list containing all the answers. You'll still have duplicates though if you have multiple copies of the same answer within a single column.
Columns of "right, wrong1, wrong2, wrong3, wrong4" mean that you have a mis-designed database. In general, a number or letter suffix on a column name should be a red flag to rethink the problem.
As you observed, your design required you to hack around to get a solution to a typical data reduction problem.
Is this what you wanted?
select distinct c1 from t
In MySQL AND MS SQL:
SELECT
CASE
WHEN which = 1 THEN c1
WHEN which = 2 THEN c2
WHEN which = 3 THEN c3
WHEN which = 4 THEN c4
WHEN which = 5 THEN c5
END AS answer,
which
FROM mytable, (
SELECT 1 AS which
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
) w
For Oracle, add FROM DUAL to each of the number selects.
This is the exact answer.
SELECT (ans) FROM (
SELECT correct AS ans FROM tGeoQuiz
UNION
SELECT wrong1 AS ans FROM tGeoQuiz
UNION
SELECT wrong2 AS ans FROM tGeoQuiz
UNION
SELECT wrong3 AS ans FROM tGeoQuiz
UNION
SELECT wrong4 AS ans FROM tGeoQuiz
) AS Temp