How to sort by numbers first with Oracle SQL query?

I have this table with a 'title' field which is varchar2 and I want to select all rows and sort them first by number and then by the alphabet as it normally happens.

For instance, I currently get this using a simple ORDER BY title in the end:

  • Abc
  • Def
  • 321

But I want this:

  • 321
  • Abc
  • Def

The weird thing is that SQL Developer shows the "right" order, with numbers first. But on my app (PHP using OCI8) it shows numbers last.

Answers


Not an Oracle expert, but you are supposed to be able to do it without altering the session with

SELECT * FROM my_data SORT by NLSSORT(title,’NLS_SORT=BINARY_AI’)

where you can change the NLS_SORT= to fit your needs (here are the list of values)

Keep in mind that docs says that this will force table scan, so it might be beneficial to filter them first (but if you are selecting all the table scan is what you are going to use anyway).

The reason why SQL Developer exhibits different behaviour is probably because it changes the session.


Need Your Help

PHP Session Management + AWS ElasticCache & DynamoDB

php session memcached amazon-dynamodb amazon-elasticache

I've been about to implement an AWS remote session management solution and I've been reading others experiences on the Internet.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.