Avoid duplicates rows which have one different column value in SQL Server

Question

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.

What are other possible values for mark column besides C and N? — peterm
– peterm, Commented Nov 23, 2013 at 8:12

Mureinik · Accepted Answer · 2013-11-23 08:18:31Z

1

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

answered Nov 23, 2013 at 8:18

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Avoid duplicates rows which have one different column value in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related