jon.nicholssoftware.com jon.nicholssoftware.com - 4 months ago 25
SQL Question

Group By where columns may be ambiguous

Lets say I have the below table

SurveyID, Question 1, Question 2
--------------------------------
1 | 5 | NULL
1 | NULL | 9
2 | 7 | NULL
2 | NULL | 10
3 | 9 | NULL
3 | NULL | 10


How Can I GROUP BY SurveyID to get 1 record for both Question 1 & 2?

I've tried...

SELECT SurveyID, [Question 1], [Question 2]
GROUP BY SurveyID, [Question 1], [Question 2]


Which gives me the above result, where as I'm looking for the one below.

SurveyID, Question 1, Question 2
--------------------------------
1 | 5 | 9
2 | 7 | 10
3 | 9 | 10

Answer

Rules for data retrieval are not specified, so I'm assuming you'd like to obtain maximum value for each surveyid. If that's the case, use aggregate function max:

select 
  surveyid,
  max([Question 1]) as [Question 1], 
  max([Question 2]) as [Question 2]
from yourtable
group by surveyid

Notice that the GROUP BY clause now only contains the surveyid as for each value we would like to get one record in the output.