Search code examples
sqloracle-databasesql-order-bydistinctoracle9i

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?

Solution

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