| Subject | Re: [firebird-support] Re: wrong ordering in WITH RECURSIVE query |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2019-04-16T07:33:32Z |
It's your query that is wrong, it's the final result that should be ordered, not the subselect, i.e.:
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
)
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
)
SELECT * FROM n
order by poradi
Set
tir. 16. apr. 2019 kl. 02:42 skrev respektive@... [firebird-support] <firebird-support@yahoogroups.com>:
Hi all,it is ordered according to order of insertsI have tried to delete one row and reinsert it - and the row is now last :-(So is there some logical error in my query?Or is it the error in fb engine?Thank you a lot.Tom