Menu
I have a table: abc_test with columns n_num, k_str.
This query doesnt work:
select distinct(n_num) from abc_test order by(k_str)
But this one works:
select n_num from abc_test order by(k_str)
How do DISTINCT and ORDER BY keywords work internally that output of both the queries is changed?
421
All titles are distinct. There is no way this query can be executed reasonably. Either DISTINCT doesn't work (because the added extended sort key column changes its semantics), or ORDER BY doesn't work (because after DISTINCT we can no longer access the extended sort key column).
Answer. Yes, the DISTINCT clause can be applied to any valid SELECT query. It is important to note that DISTINCT will filter out all rows that are not unique in terms of all selected columns.
If you specify multiple columns, the result set is sorted by the first column and then that sorted result set is sorted by the second column, and so on. The columns that appear in the ORDER BY clause must correspond to either column in the select list or columns defined in the table specified in the FROM clause.
Yes, DISTINCT works on all combinations of column values for all columns in the SELECT clause.
As far as i understood from your question .
distinct :- means select a distinct(all selected values should be unique). order By :- simply means to order the selected rows as per your requirement .
The problem in your first query is For example : I have a table
ID name
01 a
02 b
03 c
04 d
04 a
now the query select distinct(ID) from table order by (name) is confused which record it should take for ID - 04 (since two values are there,d and a in Name column). So the problem for the DB engine is here when you say
order by (name).........
190
You might think about using group by instead:
select n_num
from abc_test
group by n_num
order by min(k_str)
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
