SQL Server group removal from multiple columns

Let's take a look at the example below:

Table tables

Field 1 Field 2

ID Name

1 A

2 b

3 C

4 C

5 b

The library structure is probably like this, this is just a simple example, the actual situation will be much more complex.

For example, if I want to use a single statement to find all the data that name does not repeat, then you must

Use distinct to remove redundant duplicate records.

The results from the select DISTINCT name from table are:

----------

Name

A
C

It seems to be working, but what I want to get is the ID value? Change the query statement:

Select DISTINCT name, ID from table

The result would be:

----------

ID Name

1 A

2 b

3 C

4 C

5 b

Why doesn't distinct work? The role was up, but he played two of them at the same time.

fields, which must have the same ID and name, are excluded

Let's change the query statement:

Select ID, distinct name from table

Unfortunately, in addition to the error message you get nothing, distinct must be placed at the beginning. Is it difficult to put distinct in a where condition? Can, still error.

--------------------------------------------------------

The following methods are possible:

SELECT *, COUNT (distinct name) from the table group by name

Results:

ID Name count (distinct name)

1 a 1

2 B 1

3 C 1

The last item is superfluous, do not care, the purpose is to achieve

Group by must be placed before order by and limit, or it will be an error

SQL Server group removal from multiple columns