I have a view like this . I’m using SQL Server.
formId name mark 100 Rob N 101 Rob N 101 Rob C 105 Jane N 106 Jane N
view name: view_example
There, some formId’s have two kinds of mark values as N and C. and some have single values as N. I want to print this to a report if formid is duplicating with different mark values, print the raw with mark value C . If form id is not duplicating print the value as it is. out put should look like this
formId name mark 100 Rob N 101 Rob C 105 Jane N 106 Jane N
I want to write the SQL query for this.
Advertisement
Answer
You could GROUP BY
all the columns where you want to preserve all the values, and apply an aggregate function (in this case – MIN
, in order to give C
precedence over N
) on the mark
column:
SELECT formId, name, MIN(mark) AS mark FROM my_table GROUP BY formId, name