Mysql distinct how does it work

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. If you want to report an error, or if you want to make a suggestion, do not hesitate to send us an e-mail:.

Exercise: Select all the different values from the Country column in the Customers table. Report Error. Your message has been sent to W3Schools. As I expected, here is your problem. Your select column and order by column are different. Your output rows are ordered by time, but that order doesn't necessarily need to preserved in the id column. Here is an example. By "duplicates" I mean rows where all fields have the same values. For example, say you have a table that looks like:.

Note that which row actual row eg: whether it's row 2 or row 3 it selects is irrelevant, as the result would be indistinguishable. If row 1 is processed first then A , B is placed in the result set and it is associated with row 1.

Row 3 produces X , Y that is also put in the result set. But if row 2 is processed before row 1 then, following the same logic exposed in the previous paragraph, the records in the result set are sorted as X , Y , A , B.

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? Let us consider one example, Create one table names developers using the following create a query.

We have inserted multiple values in my table using the same insert format. The query statement for this will be as follows —. As we can see, all the duplicate values of technology value are skipped and only distinct and different values are added in the result set. When defined on one column it retrieves the unique values of that column. Let us clarify our concept with the help of an example. Note that here position and technology togetherly should have unique values. We can observe from the developers table records that there are two records with names Payal and Heena having the same technology and same position i.