Usage of distinct in sqlserver (non-Repeated Records)

Let's take a look at the example below:

Table

Field 1 Field 2
Id name
1
2 B
3 c
4 c
5 B

The library structure is like this. This is just a simple example, and the actual situation is much more complicated.

For example, if you want to use a statement to query all data with no duplicate names, you must

Use distinct to remove redundant duplicate records.

Select distinct name from table
The result is:

----------

Name
A

C

It seems that the effect has been achieved, but what I want to get is the id value? Modify the query statement:

Select distinct name, id from table

The result is:

----------

Id name
1
2 B
3 c
4 c
5 B

How does distinct not work? The role is played, but he has two roles at the same time.

Fields, that is, they must have the same id and name to be excluded.

Modify the query statement again:

Select id, distinct name from table

Unfortunately, you cannot get anything except the error message. You must start with distinct. Is it difficult to place distinct in the where condition? Yes. An error is returned.

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

The following method is feasible:

Select *, count (distinct name) from table group by name

Result:

Id name count (distinct name)
1 a 1
2 B 1
3 c 1

The last item is redundant, so you don't have to worry about it.

Group by must be placed before order by and limit. Otherwise, an error will be reported.