File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Sorting Strings in Oracle Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Sorting Strings in Oracle" Watch "Sorting Strings in Oracle" New topic

Sorting Strings in Oracle

Richard Ellison
Ranch Hand

Joined: Jun 04, 2008
Posts: 51
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.

Jan Cumps

Joined: Dec 20, 2006
Posts: 2565

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.

OCUP UML fundamental and ITIL foundation
Michael Angstadt
Ranch Hand

Joined: Jun 17, 2009
Posts: 277

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
Ranch Hand

Joined: Dec 16, 2003
Posts: 977
Hi Richard,

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

"Eppur si muove!"
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33130

Good use for decode!

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Jan Cumps

Joined: Dec 20, 2006
Posts: 2565

... 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

Joined: Sep 29, 2008
Posts: 12617

That might be logic I wouldn't have a problem putting in a stored proc.
I agree. Here's the link:
subject: Sorting Strings in Oracle
It's not a secret anymore!