CODEWITHSUNDEEP
oracle-databasesql-order-by
user2102665
user2102665

Reputation: 505

ORDER BY in multiple column with sorting conflict

I have below query:

SELECT * FROM
    ( select 'All' as display,'-10' as key from dual
    UNION
    select distinct COUNTRY_NAME display, COUNTRY_CODE key 
    from COUNTRY 
    where COUNTRY_CODE<>'NUL' 
    order by key
     );

It display below output:

Expected output:

Basically, the country name need to be in alphabetically order with 'All' be the first record. However both column of data seems have some conflict in each other in order to sort out accordingly.

Upvotes: 0

Views: 242

Answers (1)

APC
APC

Reputation: 146239

"the country name need to be in alphabetically order with 'All' be the first record"

Your query sorts by key only so that ORDER BY doesn't produce the order you want. To guarantee the order you want you need to put an ORDER BY clause on the outer query, and sort by key then display, like this: 

SELECT * FROM
    ( select 'All' as display,'-10' as key from dual
      UNION 
      select COUNTRY_NAME display, COUNTRY_CODE key 
      from COUNTRY 
      where COUNTRY_CODE<>'NUL'
    )
 order by decode(key, '-10', 1, 99), display;

Incidentally you don't need that DISTINCT: UNION will do that for you.

Upvotes: 2

Related Questions