Menu
When I query
select distinct Name from Emp;
Emp Table
ID Name 1 Sam 2 Tom 3 Sam
does this query return first distinct name(with ID=1) from duplicate names or last distinct name(with ID=3)
How the distinct keyword actually works in this context?
223
The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
The DISTINCT keyword in the SELECT clause is used to eliminate duplicate rows and display a unique list of values. In other words, the DISTINCT keyword retrieves unique values from a table.
As a general rule, SELECT DISTINCT incurs a fair amount of overhead for the query. Hence, you should avoid it or use it sparingly. The idea of generating duplicate rows using JOIN just to remove them with SELECT DISTINCT is rather reminiscent of Sisyphus pushing a rock up a hill, only to have it roll back down again.
Yes, DISTINCT works on all combinations of column values for all columns in the SELECT clause.
I think there's a misunderstanding here: Your query does not return the records, only the distinct column values. Which, in your example, are 'Sam' and 'Tom'.
They have no particular order which can safely be expected. It may be the natural order, or the order in which they are processed on the database (completely depending on the database implementation), or semi-random (such as iterating over items in a set). The order may also vary depending on whether the result was retreived from the data or from the cache.
If you want a particular order, then specify it as order criterium:
select distinct Name from Emp order by Name asc
If you want the distinct values and the first record containing it, use group by:
select min(ID), Name from Emp group by Name
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
