Need select distinct to show other fields. 1

[ksolomon]

Hi,
I have a query as follows:
SELECT DISTINCT Question.[Question Number] FROM Question;

Question Number is the only field that I want distinct, but I would like to show 2 other fields that go with Question Number (Question ID, and Question).

Any suggestions. This statement doesn't work:
SELECT DISTINCT Question.[Question Number], Question.[Question ID], Question.Question
FROM Question;
It give me duplicates on Question Number.

Thanks.

 

[lupins46]

By definition there must be more than one record for a given question number (otherwise you would not be using distinct).
So how do you decide which record to get the other fields from?

 

[PHV]

You may try an aggregate query:
SELECT [Question Number], First([Question ID]) AS ID1st, First(Question) AS Question1st
FROM Question
GROUP BY [Question Number];


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

Distinct returns each distinct combination

So if you have
Number ID Question
1 1 Who are you?
1 1 Where do you live?

those are two distinct records, when you compare all three field results, there is a difference in the question.

HTH

Leslie

 

[ksolomon]

I only want to select one Question 1.
For example:

Question# Question
1 What is life?
1 Where do you live?
1 Who are you?

I only want one Question 1, but I want the question displayed.

 

[lupins46]

Then PHV has given the answer.

 

[ksolomon]

Thank you all for your posts. It is working now. I wanted to randomly select a different Question 1 each time so I had to create a new table with the random order and then apply the statement that PHV gave. Thanks again.