Thanks for the explanations. I have tried adding a 3rd identity column, just numbers increasing. And the regular select shows them in the order of the identity column. This is one solution I might consider.
I have tried running select * from POS.SECTIONS_REL_ITEMS --DERBY-PROPERTIES index=null; to no avail. I still get the same results. I bet if I had INTEGER instead of VARCHAR as the 2 keys, they'd be retrieved in the same order I inserted them in just because of the natural auto-increment ordering. Though I have to use those 36 char generated keys and they may not be in proper order, that's why I can't use an ORDER BY in my select. Unless I add an extra auto-increment column as my sort key. Again thanks for the help. -- George [email protected] On Fri, Sep 11, 2009 at 12:59 PM, Knut Anders Hatlen <[email protected]>wrote: > George H <[email protected]> writes: > > > Hi, I have a strange problem and I am not sure if this is a problem with > > Insert or select or maybe I am missing something here. > > > > Apache Derby EmbeddedDriver > > 10.5.3.0 - (802917) > > > > I have 3 tables and as I insert IDs into 1 table and do a select on it, I > see > > the values in the same order I inserted them in. > > I do this for 2 tables. My third table is my relationship table which > whose > > primary keys are the keys of both tables together. > > > > When I insert rows, I do not see them in the same order I inserted them > in. > > Below I provide SQL statements that show this problem. I hope someone can > > guide me as to what is the problem and how to solve it. Many thanks in > > advance. > > > [...] > > > > select * from POS.SECTIONS_REL_ITEMS; > > > > Result > > SECTION_ID ITEM_ID > > b6c0bdf8-68c8-4882-9723-878574fefb52 > 04bc6246-02d3-43b8-81cd-399f52bc58d8 > > b6c0bdf8-68c8-4882-9723-878574fefb52 > 0784c479-7cd6-4cef-a446-f7a3724321c4 > > b6c0bdf8-68c8-4882-9723-878574fefb52 > 4e233de6-4044-4f73-9450-29833549700f > > b6c0bdf8-68c8-4882-9723-878574fefb52 > 9ee99832-c23a-41e6-bb85-e7f98035717c > > b6c0bdf8-68c8-4882-9723-878574fefb52 > f481ba35-7f49-407a-aaed-fc6213ae1b90 > > > > The Item IDs are out of order than how I inserted them in. > > The optimizer probably chooses to scan the primary key index instead of > the base table, so you'll get the results sorted the same way as in the > index. Without an ORDER BY clause, a SELECT statement is free to return > the results in any order. If it's a requirement to get the result in a > certain order, you could have an extra auto-generated column[1] in the > table and order by that column. It is also possible to use optimizer > overrides[2] to prevent the optimizer from using the index, in which case > you'll get the rows in the order they are stored in the base table. The > optimizer override would look like this: > > ij> select * from POS.SECTIONS_REL_ITEMS --DERBY-PROPERTIES index=null > ; > > > [1] http://db.apache.org/derby/docs/10.5/devguide/cdevtricks21248.html > [2] http://db.apache.org/derby/docs/10.5/tuning/ctunoptimzoverride.html > > -- > Knut Anders >
