A series of thoughts on distinct questions _mssql

Someone raised such a question, sorted out for everyone to refer to the

Suppose there is such a table as:

The data here has the following characteristics: In a departmentid, there may be more than one name, and vice versa. This means that name and DepartmentID are many-to-many relationships.

Now you want to implement a query that follows DepartmentID (the first step), then gets the distinct value of the Name column (step two), and retains the relative order after the first step. In this case, three values should be returned:ACB

The first thing we'll think of is the following wording

Select DISTINCT name from Sample ORDER by DepartmentID

Semantically, this is natural. Unfortunately, this statement cannot be executed at all, and the error message is:

What this mistake means is that If you use distinct (to repeat the value), the field that appears after the by-by-clause must also appear behind the Select, but if the DepartmentID does appear behind the Select, there is obviously no duplicate value, so the result is certainly wrong.

Select distinct Name,departmentid from Sample order by DepartmentID

So, since disinct is a problem with this in combination with an by-by, is it possible for us to work around it, for example:

Select distinct a.name from
(select the percent Name from Sample order by DepartmentID) a

To compare the previous wording, we used the subquery technique. Also from the semantic perspective, still hot is very straightforward. I want to sort by DepartmentID first and then repeat the value. But return to the result is the following:

Although it does go beyond duplicate values, the order of return is incorrect. We want to sort by departmentid, then remove duplicate values, and preserve the relative order after sorting.

Why is there such a result? This is because the distinct itself is sorted, and this behavior cannot be changed (as can be seen in the execution plan of the following figure). So in fact, the order by which we did before will lose its meaning here. "In fact, if you look at a similar query generated in an ORM tool such as the Ado.net Entity framework, it automatically discards the setting of the order by."

So, under such circumstances, is it impossible to achieve the demand? Although this demand is not often seen, most of the time, distinct as the last operation, to do a sort of reasonable.

I think so, since this behavior of distinct is built in, can you bypass this operation? Finally, one solution I used was: can I make a number for each name, for example, if there are two aces, the first a I'll number it for 1, the second number is 2, and so on. Then, when I query, I sort the first, and then I filter the name with the number 1, so I actually have to repeat the value.

SQL Server 2005 starts with a row_number feature that, combined with this feature, enabled me to implement the following query:

Select A.name from 
(select the Percent
Name,departmentid,row_number () over (partition by-Name order by Departm Entid) row from Sample order by
DepartmentID-a
where a.row=1 order by
A.departmentid

And then, I got the following results, which I've come up with, which should be consistent with the requirements mentioned earlier.

In comparison, the query is less efficient, which is predictable (see the clues below). But if demand is rigid, sacrificing some of the performance is not surprising. Of course, we can look again to see if there are some better formulations. In any case, implementations that use built-in standards are usually relatively fast.

The above is about distinct questions triggered a series of thinking, I hope to help you learn.