aspose file tools*
The moose likes JDBC and the fly likes Sorting Strings in Oracle Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Sorting Strings in Oracle" Watch "Sorting Strings in Oracle" New topic
Author

Sorting Strings in Oracle

Richard Ellison
Ranch Hand

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

Joined: Dec 20, 2006
Posts: 2503
    
    8

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
youtube channel
Michael Angstadt
Ranch Hand

Joined: Jun 17, 2009
Posts: 274

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.


SCJP 6 || SCWCD 5
Gian Franco
blacksmith
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
Marshal

Joined: May 26, 2003
Posts: 30758
    
156

Gian,
Good use for decode!


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

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

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: http://aspose.com/file-tools
 
subject: Sorting Strings in Oracle