views:

16883

answers:

11

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

+1  A: 

Is this what you wanted?

select distinct c1 from t

JoshBerke
Hard to tell if it is, but +1 cause it looks like it should be :)
Binary Worrier
yea it is hard to tell
JoshBerke
No, that selects distinct from one column
Bryan
Ahh ok so I understand your question now...union is your best bet;-)
JoshBerke
+1  A: 

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.

scwagner
A: 

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.

Quassnoi
A: 

EDIT: Great. achinda99 had to go and prove me wrong; not once, but TWICE. Thanks a lot.

Andrew
yes, that is what I want to do.
Bryan
It is possible. The two answers I gave both work.
achinda99
+6  A: 

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
achinda99
this looks good. I will try it now.
Bryan
Try the pivot one I just did below. I think that's much better. I'd read on whats more efficient though, I don't know too much about the internal functioning of the different database servers.
achinda99
Brilliant. Wish I'd thought of that.
Andrew
+3  A: 

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;
achinda99
why is this much better?
Bryan
I like this one better because logically, I'm doing what I want to do. The first query just achieves it, where as this follows my though process. Also, less select subqueries.
achinda99
+6  A: 
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)
Kristen
Yeah, you're right. I was wrong on that. I tested both and it gave me similar results on my dataset but I wasn't sure of the specifics so if there were duplicates DISTINCT would have taken care of it. I've never used a UNION in any real work I've done. Just educational purposes.
achinda99
You are correct! Distinct not necessary.
Bryan
No worries! Worth remembering to always use UNION ALL when you know that there are no duplicates (or you WANT any duplicates). Saves the server having to do a SORT and De-DUPE
Kristen
A: 

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 
Bryan
A: 

Hi achinda99,

I am having trouble to apply your logic to the following table...can you help me out..

I have a user table with five columns

ID Code FirstName LastName MiddleInitial LocationCode
1 123    XFName    XLName     M    AAA
2 123    XFName    XLName    M     BBB
3 124    YFName    YLName    L     CCC
4 124    YFName    YLName    L     DDD
5 124    YFName    YLName    L     AAA

And have to select Distinct Users based on their Code,FirstName,LastName,MiddleInitial.

My out should be
1 123 XFName XLName M
2 124 YFName YLName L

Thanks In advance...

porhills
You are far more likely to get a response if you post this as a new question, with a link to this question. (Then come back and delete this "answer" from this question.)
ScottJ
A: 

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.

Randal Schwartz
You are a goofball. For a simple quiz there is nothing wrong with creating a quick table of questions. Trying to sound smarter than... we are are we?
Bryan
A: 

@Randal Schwartz - second that

kralco626