jon.nicholssoftware.com jon.nicholssoftware.com - 15 days ago 6
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.

Comments