CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-apex
Usman YousafZai
Usman YousafZai

Reputation: 1120

Order By in Ascending Order

i have used the following query for POPUP LOV in Apex 

select VEN_INVOICE_REFNO as display_value, VEN_INVOICE_REFNO as return_value 
  from VENDORINVOICE
 order by 1 asc;

i want to show me the values as shown in diagram in numeric order i-e in ascending order.i have tried all the possible ways which i know but it don't works.

enter image description here

Upvotes: 0

Views: 244

Answers (5)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

The other solutions here will not work if any of the records have a non-numeric value, so FWIW here is an alternative solution:

select VEN_INVOICE_REFNO as display_value, VEN_INVOICE_REFNO as return_value 
from VENDORINVOICE
order by LPAD(VEN_INVOICE_REFNO,100,' ') asc;

Just change 100 to the maximum size of the column.

However, my solution will not work if the column has any negative or decimal (floating-point) numeric values.

Upvotes: 1

mrnaza
mrnaza

Reputation: 11

try this

select VEN_INVOICE_REFNO as display_value, VEN_INVOICE_REFNO as return_value from VENDORINVOICE order by VEN_INVOICE_REFNO

Upvotes: 0

Andrew
Andrew

Reputation: 7768

I remember I had this issue once, and I ended up multiplying it by 1

something like that (Not sure if it will work, you might need to cast your VEN_INVOICE_REFNO it to numeric)

select (VEN_INVOICE_REFNO * 1) as display_value, VEN_INVOICE_REFNO as return_value 
  from VENDORINVOICE
 order by 1 asc;

of like this:

select CONVERT((VEN_INVOICE_REFNO * 1),UNSIGNED INTEGER) as display_value, VEN_INVOICE_REFNO as return_value 
  from VENDORINVOICE
 order by 1 asc;

Upvotes: 1

Sashi Kant
Sashi Kant

Reputation: 13465

Try this ::

select VEN_INVOICE_REFNO as display_value, VEN_INVOICE_REFNO as return_value 
  from VENDORINVOICE
 order by ORDER BY CAST(`display_value` AS SIGNED)

Upvotes: 1

Rab
Rab

Reputation: 35572

it seem that your field datatype is string, convert it into numeric then apply the order by

select CAST(VEN_INVOICE_REFNO AS INTEGER) as display_value, VEN_INVOICE_REFNO as return_value 
 from VENDORINVOICE
 order by 1 asc;

Upvotes: 1

Related Questions