Menu
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
692
Select with distinct on all columns of the first query. Select with distinct on multiple columns and order by clause. Count() function and select with distinct on multiple columns.
SELECT DISTINCT FIELD1, FIELD2, FIELD3 FROM TABLE1 works if the values of all three columns are unique in the table. If, for example, you have multiple identical values for first name, but the last name and other information in the selected columns is different, the record will be included in the result set.
Answer. Yes, the DISTINCT clause can be applied to any valid SELECT query. It is important to note that DISTINCT will filter out all rows that are not unique in terms of all selected columns.
Yes, DISTINCT works on all combinations of column values for all columns in the SELECT clause.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
