Get records in Alphabetical order with sql query oracle

Question

I want to get the output of y select list in alphabetical order, below is my select query

select name, id from hes_std_name_id
where id = 121
order by name asc;

Here my output:


AHAS
BETA
aa
bios
cash
deposit

I want output as below:


aa
AHAS
BETA
bios
cash
deposit

Does this answer your question? [how to sort by case insensitive alphabetical order using COLLATE NOCASE](https://stackoverflow.com/questions/19375330/how-to-sort-by-case-insensitive-alphabetical-order-using-collate-nocase) — MT0, Apr 25 '23 at 10:13

score 1 · Accepted Answer · answered Apr 25 '23 at 05:04

It appears that you just want to do a case-insensitive sort. The simplest option would be to

order by upper(name) asc

Potentially, you could also look into setting the nls_comp and nls_sort for your session to LINGUISTIC and BINARY_CI so that all queries do case-insensitive searches and sorts. But that has much wider-ranging impacts including on the optimizer's ability to use indexes created in sessions with different nls_comp and nls_sort settings.

score 0 · Answer 2 · answered Apr 25 '23 at 15:50

Try to look at it through ASCII table. If your sample data is:

WITH
    tbl (A_TEXT) AS
        (
            Select 'AHAS'    From Dual Union All
            Select 'BETA'    From Dual Union All
            Select 'aa'      From Dual Union All
            Select 'bios'    From Dual Union All
            Select 'cash'    From Dual Union All
            Select 'deposit' From Dual 
        )

then (for just 1st two letters) the ASCII representation of your query, with Order By clause as in the question, would be like:

--  Your Order By clause
Select     A_TEXT,
          '--->' "AS_IS",
          SubStr(A_TEXT, 1, 1) || '_' || SubStr(A_TEXT, 2, 1) "TXT",
          ASCII(SubStr(A_TEXT, 1, 1))  || '_' || ASCII(SubStr(A_TEXT, 2, 1)) "AS_IS_ASCII"
From      tbl
Order By  A_TEXT

A_TEXT  AS_IS TXT AS_IS_ASCII 
------- ----- --- ------------
AHAS    --->  A_H 65_72       
BETA    --->  B_E 66_69       
aa      --->  a_a 97_97       
bios    --->  b_i 98_105      
cash    --->  c_a 99_97       
deposit --->  d_e 100_101

... but if you use Upper() or Lower() function in Order By clause it would be like below (1st two letters again):

-- Using Upper() or Lower()
Select    ASCII(SubStr(Upper(A_TEXT), 1, 1)) || '_' || ASCII(SubStr(Upper(A_TEXT), 2, 1)) "UPPER_ASCII",
          SubStr(Upper(A_TEXT), 1, 1) || '_' || SubStr(Upper(A_TEXT), 2, 1) "UPP",
          '<---' "UPPER",
          A_TEXT,
          '--->' "LOWER",
          SubStr(Lower(A_TEXT), 1, 1) || '_' || SubStr(Lower(A_TEXT), 2, 1) "LOW",
          ASCII(SubStr(Lower(A_TEXT), 1, 1))  || '_' || ASCII(SubStr(Lower(A_TEXT), 2, 1)) "LOWER_ASCII"
From      tbl
Order By  Lower(A_TEXT)  -- or Upper(A_TEXT)

UPPER_ASCII     UPP UPPER  A_TEXT  LOWER  LOW    LOWER_ASCII 
--------------- --- ----  ------- ------- ------ ----------------
65_65           A_A <---    aa    --->    a_a    97_97           
65_72           A_H <---   AHAS   --->    a_h    97_104          
66_69           B_E <---   BETA   --->    b_e    98_101          
66_73           B_I <---   bios   --->    b_i    98_105          
67_65           C_A <---   cash   --->    c_a    99_97           
68_69           D_E <---  deposit --->    d_e    100_101

Get records in Alphabetical order with sql query oracle

2 Answers2