SELECT DISTINCT Major, Major_category
What I expected to happen:
only the unique values from ‘Major_caegory’ column to be displayed
What actually happened:
Instead of getting, only the unique values from 'Major_caegory' column, we are getting repeated values.
PETROLEUM ENGINEERING Engineering
MINING AND MINERAL ENGINEERING Engineering
METALLURGICAL ENGINEERING Engineering
NAVAL ARCHITECTURE AND MARINE ENGINEERING Engineering
CHEMICAL ENGINEERING Engineering
want to know, what happens behind the scenes when we are using DISTINCT on multiple columns.
so shouldn’t the major_Category column contain only unique values? we see Engineering repeated even after using DISTINCT.
However if we just use the code SELECT DISTINCT Major_category the results are fine. only when we are using DISTINCT with multiple columns like in the above example we get results with repeated values.
so wonder how DISTINCT works when used with multiple columns.
No. When used with multiple columns,
DISTINCT looks at both the columns instead of looking at them one-by-one.
So, if you had something like -
DISTINCT on both columns would return
Because there were two (A, B)s, it only selects one of those because it looks at both the columns at the same time.
Make sense now. thank you for the explanation. appreciate it.