Where is distinct

Interview Questions. Company Questions. Artificial Intelligence. Cloud Computing. Data Science. Angular 7. Machine Learning. Data Structures. Operating System. Computer Network. Compiler Design. Computer Organization. Discrete Mathematics. Ethical Hacking. Computer Graphics. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 10 years, 7 months ago.

Active 2 years, 5 months ago. Viewed k times. Improve this question. Michael Petrotta Mohit Mohit 1, 2 2 gold badges 12 12 silver badges 18 18 bronze badges. That's contradiction in terms. Add a comment. Active Oldest Votes. Improve this answer. Adam Matan Adam Matan k gold badges silver badges bronze badges. Even I was thinking initially what you were thinking. But, tried executing the query. It is wrong. Only partitions with a single row in them, i. There will be times in which two statements will return the same results but with widely different execution times.

The simple PETS dataset was manually created by this author. However, there are animal shelters that might have similar data. They might want to know the average age of a given pet. Does the following T-SQL generate the same results? The image below shows two different averages. Therefore, the results are not the same.

We have two animals that are exactly 4 years old. This removal causes the two statements to come up with different results. What happens if we ask for distinct average by pet type? Since the duplicate age was between one cat record and one dog record, the duplicate records issue is removed. There are no longer any duplicate cat or dog records. If we look at the actual query plan, we will see that same set of operators are used. In general, any type of sort is an expensive operation.

Does the query plan different between these two statements? Not only are the actual execution plans different. But the first statement executes 4 times faster than the second statement. Again, any type of sort is an expensive operation. The first statement just counts the number of records. Then, it counts the number of records while tossing any duplicate or NULL values.

Let us now revisit the idea that the Adventure Works company sample database has too many job titles for the number of employees. The image below shows my remote desktop connect to this virtual machine. The first query a developer might try is to get a count of job titles as seen below.

However, this statement does not show the break down by employees by job title. Our second attempt to write the query might be a distinct selection of job titles. The results of the two queries does show us that the employees have 67 job titles. That is a lot of job titles. But it does not show the job titles that are seldomly used.

These are probably the ones that HR might discontinue. The best queries are to count the number of employees by job title. The two statements are listed below that return the same results and have the same execution plan. The output of the first query is below. It might be wiser choice to sort by Total column ascending. This would list all the titles that are seldomly used. In a nutshell, we can supply the Human Resources Department with a list of Job Titles by number of employees who have that title.

The Finance Department at Adventure Works wants to know how many international orders were processed in There is a cost associated with using a currency transaction service for on-line orders.