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.

CREATE TABLE POS.TEST_SECTIONS
(
    SECTION_ID CHAR(36) NOT NULL,
    SECTION_NAME VARCHAR(255) NOT NULL,
    PRIMARY KEY(SECTION_ID)   
);

CREATE TABLE POS.TEST_ITEMS
(
    ITEM_ID CHAR(36) NOT NULL,
    ITEM_NAME VARCHAR(255) NOT NULL,
    PRIMARY KEY(ITEM_ID)
);

CREATE TABLE POS.SECTIONS_REL_ITEMS
(
    SECTION_ID CHAR(36) NOT NULL,
    ITEM_ID CHAR(36) NOT NULL,   
    FOREIGN KEY(SECTION_ID) REFERENCES POS.TEST_SECTIONS(SECTION_ID),   
    FOREIGN KEY(ITEM_ID) REFERENCES POS.TEST_ITEMS(ITEM_ID),
    PRIMARY KEY(SECTION_ID,ITEM_ID)
);

INSERT INTO POS.TEST_SECTIONS(SECTION_ID, SECTION_NAME) VALUES('b6c0bdf8-68c8-4882-9723-878574fefb52','Section 1');
INSERT INTO POS.TEST_SECTIONS(SECTION_ID, SECTION_NAME) VALUES('4e956141-77ef-48d3-adc8-ae617f4352fb','Section 2');
INSERT INTO POS.TEST_SECTIONS(SECTION_ID, SECTION_NAME) VALUES('2100a63e-078c-41b0-9683-1e6ad88104c9','Section 3');
INSERT INTO POS.TEST_SECTIONS(SECTION_ID, SECTION_NAME) VALUES('76dfcf3b-7705-4933-84b6-bfee0f0f98d2','Section 4');
INSERT INTO POS.TEST_SECTIONS(SECTION_ID, SECTION_NAME) VALUES('90b94bce-4223-4f91-a917-7363672b5efd','Section 5');

INSERT INTO POS.TEST_ITEMS(ITEM_ID, ITEM_NAME) VALUES('0784c479-7cd6-4cef-a446-f7a3724321c4','Item 1');
INSERT INTO POS.TEST_ITEMS(ITEM_ID, ITEM_NAME) VALUES('04bc6246-02d3-43b8-81cd-399f52bc58d8','Item 2');
INSERT INTO POS.TEST_ITEMS(ITEM_ID, ITEM_NAME) VALUES('f481ba35-7f49-407a-aaed-fc6213ae1b90','Item 3');
INSERT INTO POS.TEST_ITEMS(ITEM_ID, ITEM_NAME) VALUES('9ee99832-c23a-41e6-bb85-e7f98035717c','Item 4');
INSERT INTO POS.TEST_ITEMS(ITEM_ID, ITEM_NAME) VALUES('4e233de6-4044-4f73-9450-29833549700f','Item 5');

INSERT INTO POS.SECTIONS_REL_ITEMS(SECTION_ID,ITEM_ID) VALUES('b6c0bdf8-68c8-4882-9723-878574fefb52','0784c479-7cd6-4cef-a446-f7a3724321c4');
INSERT INTO POS.SECTIONS_REL_ITEMS(SECTION_ID,ITEM_ID) VALUES('b6c0bdf8-68c8-4882-9723-878574fefb52','04bc6246-02d3-43b8-81cd-399f52bc58d8');
INSERT INTO POS.SECTIONS_REL_ITEMS(SECTION_ID,ITEM_ID) VALUES('b6c0bdf8-68c8-4882-9723-878574fefb52','f481ba35-7f49-407a-aaed-fc6213ae1b90');
INSERT INTO POS.SECTIONS_REL_ITEMS(SECTION_ID,ITEM_ID) VALUES('b6c0bdf8-68c8-4882-9723-878574fefb52','9ee99832-c23a-41e6-bb85-e7f98035717c');
INSERT INTO POS.SECTIONS_REL_ITEMS(SECTION_ID,ITEM_ID) VALUES('b6c0bdf8-68c8-4882-9723-878574fefb52','4e233de6-4044-4f73-9450-29833549700f');

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.
--
George
george.dma@gmail.com