CODEWITHSUNDEEP
sqloracle-databasesortingoracle11gsql-order-by
Olahzzz
Olahzzz

Reputation: 571

Sort Alphanumeric column in Oracle SQL

I am not an SQL expert. I've been searching over google on how to achieve below. I need to sort my records based on ID. (prefix first and then their numerical values)

Table: CUSTOMER_TRANS

| ID       | Name   | Date       |
|==========|========|============|
|CP-091435 | Ola    | 01-01-2010 |
|WM-183258 | Tor    | 09-09-2001 |
|CP-109056 | Jess   | 03-03-2003 |


SELECT * FROM CUSTOMER_TRANS ORDER BY substr(ID, 4) desc;

I need to sort first the 2 prefix e.g ES and then the numerical values. However, my SQL above returns only the numerical highest WM-183258. The expected result is to returns first the "CP" prefix and the highest in numerical value e.g. below. Hope someone can give me somelight.

Expected Result:

| ID       | Name   |
|==========|========|
|CP-109056 | Ola    | 
|CP-091435 | Jess   | 
|WM-183258 | Tor    |

Upvotes: 3

Views: 4554

Answers (3)

Stephen Caggiano
Stephen Caggiano

Reputation: 166

I think the best way to make the code understandable the next person is to use virtual columns

Alter table CUSTOMER_TRANS add(prefix generated always as (substr(longid,1,2)) virtual,
                     suffix generated always as (substr(longid,4)) virtual);

Then you can just "Order by prefix, suffix desc

Upvotes: 1

Phil
Phil

Reputation: 164736

My PL/SQL is quite rusty but you should be able to use something like 

... ORDER BY substr(ID, 1, 2) ASC, substr(ID, 4) DESC

or even better as pointed out by mathguy

... ORDER BY substr(ID, 1, 2) ASC, ID DESC

That is, sort by the first two characters ascending, then by the rest descending.

That's probably quite sub-optimal from a performance perspective. I would consider breaking up that ID into it's parts, eg

ID_PREFIX CHAR(2),
ID_SUFFIX CHAR(6) -- or a numeric type, whatever is appropriate

and create your primary key on both. That makes it easy to group and sort and for display, you can just use

SELECT ID_PREFIX || '-' || ID_SUFFIX AS ID...

Upvotes: 3

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

You may try this one :

with CUSTOMER_TRANS(Id,Name) as
(
 select 'CP-091435','Ola' from dual union all    
 select 'WM-183258','Tor' from dual union all
 select 'CP-109056','Jess' from dual   
)
select *
  from CUSTOMER_TRANS
 order by substr(ID, 1, 2), substr(ID,4,length(ID)) desc;

| ID       | Name   |
|==========|========|
|CP-109056 | Jess   | 
|CP-091435 | Ola    | 
|WM-183258 | Tor    |

Upvotes: 1

Related Questions