Usage of distinct in SQL Server (non-duplicate records) _mssql

Let's take a look at the following examples:

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 complicated.

For example, I would like to use a statement query to get the name does not duplicate all the data, it must

Use distinct to remove excess duplicate records.

Select DISTINCT name from table
The results obtained 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 will be:

----------

ID Name
1 A
2 b
3 C
4 C
5 b

Why didn't distinct play a role? The effect was up, but he also played two

Fields, that is, must have the same ID and name to be excluded

We will change the query statement:

Select ID, distinct name from table

Unfortunately, you can't get anything except the wrong message, distinct must be placed at the beginning. Is it hard to put distinct in the where? Can, still error.

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

The following method works:

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

Results:

ID Name count (distinct name)
1 a 1
2 B 1
3 C 1

The last one is superfluous, no need to control on the line, to achieve

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