MS-SQL question about GROUP BY

[denitram]

Hi,

I have a table that has email, q1,q2 etc. I would like to select each email and the result should have each email listed once with the values of q1,q2,q3 & q4.

For example:
email-------------q1---q2
a@domain-----a
b@domain-----a
a@domain-------------b
b@domain-------------a

the result should be:
email------------q1---q2
a@domain-----a----b
b@domain-----a----a

with the query:

SELECT email, q1, q2, q3, q4 FROM my_table
GROUP BY email
ORDER BY email

I get the following error:
"Column 'mytable.q1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

and this query gives every back without grouping:

SELECT email, q1, q2, q3, q4 FROM my_table
GROUP BY email,q1,q2,q3,q4
ORDER BY email

How can I resolve my problem?

Thanx in advance.

Denitram

[almaz]

Solution depends on what do you expect to get when there are several records for particular email with different values for q1, i.e.

email     q1  q2
a@domain  a
a@domain  b

Following solution gets a maximum from "a" and "b" for my example.

SELECT email, max(q1), max(q2), max(q3), max(q4)
FROM my_table
GROUP BY email
ORDER BY email

[denitram]

Solution depends on what do you expect to get when there are several records for particular email with different values for q1, i.e.

email     q1  q2
a@domain  a
a@domain  b

Following solution gets a maximum from "a" and "b" for my example.

SELECT email, max(q1), max(q2), max(q3), max(q4)
FROM my_table
GROUP BY email
ORDER BY email

Thank you a lot, it's working