>Thanks Svein for your reply which I have pasted below
>
>Yes, I have tried iLevel, iSrNo but the order all went wrong. A small eg. is as below:
>
>Present state:
>iID, iPID, cName, iSrNo
>1, 0, Balance Sheet, 3
>2, 1, Assets, 1
>3, 2, Fixed Assets, 1
>4, 2, Current Assets, 2
>5, 1, Liabilities, 2
>6, 5, Current Liabilities, 1
>7, 5, Duties Payable, 2
>
>Now user wants to modify iSrNo thus change the order for Assets and Liabilities as below: (also eg. Current and Fixed Assets)
>1, 0, Balance Sheet, 3
>5, 1, Liabilities, 1
>6, 5, Current Liabilities, 1
>7, 5, Duties Payable, 2
>2, 1, Assets, 2
>4, 2, Current Assets, 1
>3, 2, Fixed Assets, 2
>
>I have also tried to add iSrNo with iLevel and iID in case that gave a better sort but no joy

I see. Well, I have no idea whether this will work or not (and if it works, I wouldn't be certain it would continue to work when new version of Firebird appears), but try:

WITH RECURSIVE mAccountsOrdered(iID, iPID, iSrNo) AS
(SELECT iID, iPID, iSrNo
FROM mAccounts
ORDER BY iSrNo, iID),

CTE(iID, iLevel, iSrNo, iTreeID01, iTreeID02,
iTreeID03, iTreeID04, iTreeID05, iTreeID06, iTreeID07, iTreeID08,
iTreeID09, iTreeID10) AS
(SELECT LevelThis.iID
, CAST(0 AS INTEGER)
, LevelThis.iSrNo
, LevelThis.iID
, CAST(NULL AS INTEGER)
, CAST(NULL AS INTEGER)
, CAST(NULL AS INTEGER)
, CAST(NULL AS INTEGER)
, CAST(NULL AS INTEGER)
, CAST(NULL AS INTEGER)
, CAST(NULL AS INTEGER)
, CAST(NULL AS INTEGER)
, CAST(NULL AS INTEGER)
FROM mAccountsOrdered LevelThis
WHERE LevelThis.iPID = 0
UNION ALL
SELECT LevelCurr.iID
, LevelParent.iLevel + 1
, LevelCurr.iSrNo
, LevelParent.iTreeID01
, CASE WHEN LevelParent.iLevel = 0 THEN LevelCurr.iID
else LevelParent.iTreeID02 END
, CASE WHEN LevelParent.iLevel = 1 THEN LevelCurr.iID
else LevelParent.iTreeID03 END
, CASE WHEN LevelParent.iLevel = 2 THEN LevelCurr.iID
else LevelParent.iTreeID04 END
, CASE WHEN LevelParent.iLevel = 3 THEN LevelCurr.iID
else LevelParent.iTreeID05 END
, CASE WHEN LevelParent.iLevel = 4 THEN LevelCurr.iID
else LevelParent.iTreeID06 END
, CASE WHEN LevelParent.iLevel = 5 THEN LevelCurr.iID
else LevelParent.iTreeID07 END
, CASE WHEN LevelParent.iLevel = 6 THEN LevelCurr.iID
else LevelParent.iTreeID08 END
, CASE WHEN LevelParent.iLevel = 7 THEN LevelCurr.iID
else LevelParent.iTreeID09 END
, CASE WHEN LevelParent.iLevel = 8 THEN LevelCurr.iID
else LevelParent.iTreeID10 END
FROM CTE LevelParent
JOIN mAccountsOrdered LevelCurr
ON LevelParent.iID = LevelCurr.iPID
WHERE LevelParent.iLevel < 10)

SELECT CTE.iID
, CAST(CTE.iLevel + 1 AS INTEGER) AS iLevel
, CTE.iSrNo
, CTE.iTreeID01
, CTE.iTreeID02
, CTE.iTreeID03
, CTE.iTreeID04
, CTE.iTreeID05
, CTE.iTreeID06
, CTE.iTreeID07
, CTE.iTreeID08
, CTE.iTreeID09
, CTE.iTreeID10
, TRIM(mAccounts.cName)
|| CASE WHEN COALESCE(mAccounts.iCityID, 0) = 0 THEN ''
ELSE ', ' || TRIM(mLookups.vValue) END AS cNameCity
FROM CTE
JOIN mAccounts
ON CTE.iID = mAccounts.iID
LEFT JOIN mLookups
ON mLookups.iID = mAccounts.iCityID

My wild thought (I've never tried) is that maybe prior ordering accidentally is partially preserved through a recursive CTE. Even if it gets the correct ordering, I fear that it might be slow on large result sets, so this is really a wild guess...

HTH,
Set