DenStudent DenStudent - 22 days ago 6
SQL Question

SQL Server - group by ID if column contains a value

I have following table:

ID | NR | Status
1000 | 1 | A
1000 | 2 | A
1001 | 3 | A
1002 | 4 | A
1002 | 5 | N
1003 | 6 | N


I need to an output which groups these by
ID's
. The
NR
column can be ignored. If one of the records with those
ID's
contains Status A, That
status
will be given as result.

So my output would be:

ID | Status
1000 | A
1001 | A
1002 | A
1003 | N


Any suggestions/ideas?

Answer

Do a GROUP BY, use MIN() to pick minimum status value for each id, and A < N!

select id, min(status)
from tablename
group by id