Hi,

I have table with tree structure of records, so I use select to read them in this way.

The best is with recursive n (....) and it works usually fine.

But one customer reported me failure in ordering - one node is not ordered according tu ORDER BY column - it is allways the last record, independently on the value of column PORADI

This is the query - uroven (LEVEL) is generated by query

with recursive n (ID_PUJC, NAZEV, PORADI, FK_NADR, uroven)
as (
  SELECT ID_PUJC,    NAZEV,    PORADI,    FK_NADR,    0 FROM def_pujc_test WHERE fk_nadr is null
  UNION ALL
  SELECT dp.ID_PUJC, dp.NAZEV, dp.PORADI, dp.FK_NADR, n.uroven + 1 FROM def_pujc_test dp, n WHERE dp.fk_nadr = n.id_pujc order by poradi
) SELECT * FROM n

You can reproduce it using this script

CREATE TABLE DEF_PUJC_TEST (
    ID_PUJC  SMALLINT,
    NAZEV    VARCHAR(600),
    PORADI   SMALLINT,
    FK_NADR  SMALLINT
);

INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (102, 'Bohuslavice', 100, 510);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (86, 'Vymenne fondy', 0, 0);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (85, 'Breznice', 105, 510);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (100, 'Brezuvky', 110, 510);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (112, 'Dobrkovice', 115, 510);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (111, 'Doubravy', 120, 510);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (103, 'Hrivinuv Ujezd', 125, 510);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (113, 'Kelniky', 130, 510);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (108, 'Lipa', 135, 510);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (109, 'Podkopna Lhota', 140, 510);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (104, 'Rackova', 150, 510);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (105, 'Sehradice', 155, 510);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (106, 'Slopne', 160, 510);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (110, 'Sarovy', 165, 510);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (82, 'Tecovice', 170, 510);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (97, 'Trnava', 175, 510);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (98, 'Velky Orechov', 180, 510);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (107, 'Vesela', 185, 510);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (99, 'Vsemina', 190, 510);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (83, 'Zelechovice', 0, 0);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (101, 'Provodov', 145, 510);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (0, 'Knihovna', 0, NULL);
INSERT INTO DEF_PUJC_TEST (ID_PUJC, NAZEV, PORADI, FK_NADR)
                   VALUES (510, 'Region Zlin', 9, 0);

If you insert the rows, and then run the query, everything works fine, only "Provodov" is the last - but it should be somewhere in the middle - between "Podkopna Lhota" and "Rackova"

It looks like it order recods, according to order of inserts, not to PORADI

Thank you all in advance.

Best regards, Tomas Krejzek