Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using distinct on a column and doing order by on another column gives an error

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?

like image 421
prateek gupta Avatar asked Jan 18 '12 06:01

prateek gupta


People also ask

Can you use distinct and ORDER BY together?

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).

Can we apply distinct on two columns?

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.

How does ORDER BY works on multiple 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.

Does SELECT distinct apply to all columns?

Yes, DISTINCT works on all combinations of column values for all columns in the SELECT clause.


2 Answers

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).........

like image 190
Abhishek Bhandari Avatar answered Oct 12 '22 22:10

Abhishek Bhandari


You might think about using group by instead:

select n_num
from abc_test
group by n_num
order by min(k_str)
like image 35
Nils Magne Lunde Avatar answered Oct 12 '22 23:10

Nils Magne Lunde