Mysql distinct how does it work

Nested Join Optimization. Outer Join Optimization. Outer Join Simplification. Multi-Range Read Optimization. Constant-Folding Optimization. Function Call Optimization. Window Function Optimization. Row Constructor Expression Optimization. Avoiding Full Table Scans. Optimizing Subqueries with Materialization. Derived Condition Pushdown Optimization. Optimizing Performance Schema Queries. Optimizing Data Change Statements. Optimizing Database Privileges. Other Optimization Tips. Primary Key Optimization.

Foreign Key Optimization. Multiple-Column Indexes. SELECT DISTINCT keyword is very helpful when a table contains many duplicate values and we have to fetch unique records only, for example in business scenarios where we want to know the distinct locations from where orders are being placed, how many distinct sets of customers do we have, etc. For more simplification of its working, we may look at it in the following way:. We have successfully inserted 7 records in the table. The customers table after performing the above insertions looks something as follows:.

Find the customer ids of all the unique customers who have bought or ordered something from the departmental store. This can be observed in this example also. Find the sum of revenue collected for all the unique orders that were made on a particular date at a particular store of the departmental store.

There is no rule imposed on the database engine about the order it processes the rows when it runs a query. The database is free to process the rows in any order it consider it's better for performance. Your query is invalid SQL and the fact that it can return different results using the same input data proves it.

Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Ask Question. Asked 10 years, 1 month ago. Active 4 years, 1 month ago. Viewed 10k times. Can anyone tell me how distinct works? Based on what criteria it selects the row? Improve this question. NullUserException What are the queries you are trying to run?

I know it's too late but there is no Accepted answer yet so I want to know whether the OP still wants answer for something that is not covered in the given answers? Add a comment. Active Oldest Votes. Improve this answer.