Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Suppose I have a table containing the following data:

Name | Things
-------------
Foo  |   5
Bar  |   3
Baz  |   8

If I want to insert a row, so that the final state of the table is:

Name | Things
-------------
Foo  |   5
Qux  |   6
Bar  |   3
Baz  |   8

Is this possible?

I understand we don't typically rely on the order of rows in a table, but I've inherited some code that does precisely that. If I can insert to a location, I can avoid a significant refactor.

share|improve this question

3 Answers 3

up vote 6 down vote accepted

The code you have inherited is broken - and it always has been broken. That re-factoring you'd like to avoid needs to be done. There is no alternative to an explicit order by to guarantee the sort order of a result set and there never has been.

Who knows if the code you inherited always returned rows in the order the original developer 'expected' or not.

share|improve this answer

No, it's not possible as a theoretical matter since, as you say, the order in which rows are returned is arbitrary without an ORDER BY. There is no guarantee that the order will be stable over time.

No, it's not generally possible as a practical matter. However, there may be a couple of workarounds that you could employ.

You could rename the table to <<TABLENAME>>_OLD, create a view on top of the table named <<TABLENAME>>, and embed an ORDER BY in that view that returns the data in whatever order you want.

If the view option doesn't work (and it may be problematic if you've got a lot of data) and you're really, really, really in need a hack, you could create a new table, insert FOO (and all other rows that are before the row you want to add), insert the new row, and then insert the remaining rows. Then drop the old table and rename the new table to use the old table name. No guarantees, of course, but that would generally cause an ORDER BY-less SELECT to return the data in the order you wanted. Terribly ugly if you have a reasonable amount of data in the table.

share|improve this answer
2  
The problem with the second workaround is the same as the problem with the original code: it may seem to work today then stop working tomorrow. As you say "No guarantees" - really that code needs to be fixed. –  Jack Douglas May 19 '11 at 20:10

It depends if, in your actual data, there is any sort of ordering. If there is, you may be able to achieve this by using an index-organized table. But as the others have said, databases are not designed to implicitly guarantee an ordering.

share|improve this answer
3  
No more than you could with any other sort of table: create table my_table (id integer primary key, dummy char(1000)) organization index; insert into my_table (id, dummy) select level*2, 'A' from dual connect by level<40; commit; insert into my_table (id, dummy) values(0, 'A'); select * from my_table; first id returned: 56. The fast full scan does not follow the b-tree - it scans the blocks sequentially as they appear on the disk. –  Jack Douglas May 20 '11 at 10:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.