prateek gupta prateek gupta - 1 year ago 73
SQL Question

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?

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download