• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Sorting Problem

 
Srilakshmi Vara
Ranch Hand
Posts: 169
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

I am stuck with a small problem,

I have numeric data in a database field of type VARCHAR,

Now i need to sort on number, I tried but i have an empty record, the data is like following


Code Desc
----------

11
1010
22
33


If i sort with varchar i am getting like this , 1, 10, 2, 3.
If i sort converting to number, i am getting like this 1, 2, 3, 10, .
But i need data like this ,1, 2, 3, 10.

But i need the empty record first

Ant suggestions would be appreciated.

Srilakshmi
 
Sonny Gill
Ranch Hand
Posts: 1211
IntelliJ IDE Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you are using Oracle, use can use NVL function to get the null value as 0, then sort after converting values to number.
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
use order by to_number(column_name) nulls first, if you are using oracle.This works in oracle.

I am not sure about other databases

Shailesh
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
one more way similar to Sonny's suggestion


Shailesh
 
Srilakshmi Vara
Ranch Hand
Posts: 169
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Shailesh, nulls first worked
 
Phillip Koebbe
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Another way is:

SELECT ISNULL(field, sort value) // On SQL Server

or

SELECT IFNULL(field, sort value) // MySQL

Peace,
Phillip
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic