« Moved to a Mac  |  Main  |  Hibernate weirdness with property names »

Using Explicit Order By in your SQL Statements

Posted by: Pramod Sadalage on 09/30/2008

Recently when our test databases where upgraded new version of Oracle, we started noticing that the order in which some drop down lists were being displayed was not correct. It turns out that the SELECT statement we had, did not have a ORDER BY clause and the data was being returned in the ORDER of the creation of rows (in the order of ROWID) when the database was upgraded these ROWID's got changed and hence the ORDER of the data being shown in the drop down lists.

Lesson learnt: have a EXPLICIT ORDER BY in every SQL that provides data to be shown on the screen. In other words do not rely on the order of the result set provided by the current version of the database server. if you prefer a certain order make that choice explicit in the SQL SELECT statement.


Add To DZone

Pramod Sadalage's complete blog can be found at: http://www.sadalage.com/

About Pramod Sadalage

Pramod Sadalage

Pramod Sadalage is the co-author of the 2007 Jolt Productivity Award winning

"Refactoring Databases: Evolutionary Database Development", a Martin Fowler

signature series book and author of "Recipes for Continuous Database

Integration". Pramod works as a DBA and developer at ThoughtWorks. He works on

large custom-developed applications that use agile methodologies. While on these

projects, he pioneered the practices and processes of agility in the database.

Pramod writes and speaks about these concepts and practices to help those

interested in using agile practices on databases. He has written and presented

about database administration on XP projects, the adoption of agile processes

with databases, and the impact of agile practices on database administration and

design. Pramod is the owner and founder of the agiledatabases Yahoo! group; he

moderates group discussions and helps group members learn about agile databases.

When he is not working, you can find him spending time with his wife Rupali and

daughter Arula, and trying to improve his running.

More About Pramod »

Why Attend the NFJS Tour?

  • » Cutting-Edge Technologies
  • » Agile Practices
  • » Peer Exchange

Current Topics:

  • Languages on the JVM: Scala, Groovy, Clojure
  • Enterprise Java
  • Core Java, Java 7
  • Agility
  • Testing: Geb, Spock, Easyb
  • REST
  • NoSQL: MongoDB, Cassandra
  • Hadoop
  • Spring 3
  • Automation Tools: Git, Hudson, Sonar
  • HTML5, Ajax, jQuery, Usability
  • Mobile Applications - iPhone and Android
  • More...
Learn More »

NFJS, the Magazine

NFJS, the Magazine
December Issue Now Available

  • BDD and REST

    by Brian Sletten

  • Mocks and Stubs in Groovy Tests

    by Kenneth Kousen

  • Algorithms for Better Text Search Results

    by John Griffin

  • Knowns and Unknowns of Scrum and Agile

    by Brian Tarbox
Learn More »