• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Sorting Strings in Oracle

 
Richard Ellison
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,
Will data be retrieved from a table in Oracle in the same order that it was inserted?I have a situation where I have a table with a column "AccountType".Now this field has values like "New","Amend","Closed" etc.How can I ensure that when records are being retrieved from this table,the record with an "AccountType" value of "New" is the first row followed by the one with "Amend" etc.I know that using "Order By" in the SQL query is the way to go but I am not sure as to how it will work on Strings.Also please note that there is no other field available in the table - like a date field that may be used for Sorting.

Please advise.

Thanks
Richard
 
Jan Cumps
Bartender
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Richard Ellison wrote:Hello,
Will data be retrieved from a table in Oracle in the same order that it was inserted?I
No. The sort order when you don't provide a sort clause is 'undocumented'. You can't assume that they will be returned in a predictable fashion.

 
Michael Angstadt
Ranch Hand
Posts: 277
Eclipse IDE Java PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Like Jan said, there's no guarantee that a SELECT statement without an ORDER BY will return the rows in any particular order. This holds true no matter what database vendor you are using. I think that your best bet would be to add a column that records the time at which the row was added. Or, if the table uses a unique number for its key, you might be able to get away with just sorting by this number.
 
Gian Franco
blacksmith
Ranch Hand
Posts: 979
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Richard,

You could try to add the following clause to your select:

 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34649
365
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Gian,
Good use for decode!
 
Jan Cumps
Bartender
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
... but you would have to ammend the decode evey time a new entry is added.

[jc edit: typo. I'd better learn to write proper english)
 
David Newton
Author
Rancher
Posts: 12617
IntelliJ IDE Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That might be logic I wouldn't have a problem putting in a stored proc.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic