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

sql oracle sql-order-by distinct oracle9i
prateek gupta picture prateek gupta · Jan 18, 2012 · Viewed 28.4k times · Source

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

Abhishek Bhandari picture Abhishek Bhandari · Jan 18, 2012

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

Related questions

How to use Oracle ORDER BY and ROWNUM correctly?

I am having a hard time converting stored procedures from SQL Server to Oracle to have our product compatible with it. I have queries which returns the most recent record of some tables, based on a timestamp : SQL Server: SELECT …

Read More
Custom Order in Oracle SQL

I need to order transaction based on the currency. However I need to implement a custom order by, which makes the USD to always comes on the top, and the rest should be ordered asc. for example : BHT USD MYR …

Read More
SQL (ORACLE): ORDER BY and LIMIT

I want do sorting by property ALL data in my db and ONLY AFTER that use LIMIT and OFFSET. Query like this: SELECT select_list FROM table_expression [ ORDER BY ... ] [ LIMIT { number | ALL } ] [ OFFSET number ] I know the sorting ends …

Read More