Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Sorting with alpha first then numeric

 
Michael Arnett
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,
This is probably more of an Oracle SQL question than JDBC, but I thought I would try this forum. Has anyone ever tried to alter the default sorting scheme used by Oracle. Basically, I would like order by to return alpha first, then numeric. Its default behaviour appears to be the opposite. Thanks in advance,
-MLA
 
Dave Vick
Ranch Hand
Posts: 3244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Michael
Do you mean the order that the rows in a resultset are in? If so then you can use the ORDER BY clause. Soemthing like this:
SELECT * FROM employees ORDER BY lastName
then you can use ASC for ascending (the default) and DESC for descending order.
hope that is what you were looking for.
 
Michael Matola
whippersnapper
Ranch Hand
Posts: 1826
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave,
I think Michael had in mind changing how the values in a single text field sort alphabetically.
Oracle default:
321contact
@home
dave
mike
Desired:
dave
mike
321contact
@home
Ok, Michael, here's my quick-and-dirty, totally cheesemeister, don't-do-this-in-the-real-world, thought-this-up-in-5-minutes way of doing this:
select field , case when substr( field , 1 , 1 ) between 'A' and 'z' then 1 else 2 end "firstSortBy"
from table1
order by 2 , 1
[ June 05, 2002: Message edited by: Michael Matola ]
 
Michael Matola
whippersnapper
Ranch Hand
Posts: 1826
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, 5 more minutes thought gives you a slightly cleaner:
select field
from table1
order by case when substr( field , 1 , 1 ) between 'A' and 'z' then 1 else 2 end , field
[ June 05, 2002: Message edited by: Michael Matola ]
 
Michael Matola
whippersnapper
Ranch Hand
Posts: 1826
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And you do realize what's wrong with this is that if values match on the first character, they'll get sorted in the default order on the second, and so on.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic