Java EE (Java Enterprise Edition) General Discussion

Hi,

Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
Also post the exact results you want from that data, and an explanation of how you get those results from that data, with specific examples.
If you can show what you need to do using commonly available table (like those in the scott schema), then you don't need to post any sample data.  Just make it clear which tables you're using, and post the results and explanation.
Always say which version of Oracle you're using (for example, 12.2.0.1.0).
See the forum FAQ:

So, you want siblings sorted one way on lvl = 1, and a different way on lvl > 1; is that it?
You already found that  SEARCH ... SET  gets the right order for lvl > 1.  Let's call that value ordr_2.  What you need is another sort criterion that applies to the roots, and is inherited by all of their descendants.  Let's call this new value ordr_1.  Sort by ordr_1 first, then by ordr_2.

Since I don't have your table, I'll use the scott.emp table to illustrate.  Say we want to get all the rows with job='MANAGER' and their descendants.  We want siublings in alphabetic order by ename, but we want the roots (that ism, the 'MANAGER's) in order by empno.  Here's one way to do that:

WITH    tree (empno, ename, mgr, lvl, ordr_1)    AS
(
    SELECT  empno, ename, mgr
    ,       1
    ,       ROW_NUMBER () OVER (ORDER BY empno)
    FROM    scott.emp
    WHERE   job  = 'MANAGER'
UNION ALL
    SELECT  n.empno, n.ename, n.mgr
    ,       p.lvl + 1
    ,       p.ordr_1
    FROM    scott.emp  n   -- n for New
    JOIN    tree        p   -- p for Previous
                           ON  p.empno  = n.mgr
)   SEARCH DEPTH FIRST BY ename  SET ordr_2
SELECT    *
FROM      tree
ORDER BY  ordr_1, ordr_2
;

Output:

     EMPNO ENAME             MGR        LVL     ORDR_1     ORDR_2
---------- ---------- ---------- ---------- ---------- ----------
      7566 JONES            7839          1          1          9
      7902 FORD              7566          2          1          10
      7369 SMITH            7902          3          1          11
      7788 SCOTT            7566          2          1          12
      7876 ADAMS            7788          3          1          13
      7698 BLAKE            7839          1          2          1
      7499 ALLEN            7698          2          2          2
      7900 JAMES            7698          2          2          3
      7654 MARTIN            7698          2          2          4
      7844 TURNER            7698          2          2          5
      7521 WARD              7698          2          2          6
      7782 CLARK            7839          1          3          7
      7934 MILLER            7782          2          3          8

Notice that the roots, 'JONES', 'BLAKE' and 'CLARK', come in order by empno (7566, then 7698, then 7782), but, on every level after that, the siblings are in alphabetic order (e.g. 'WARD', with empno 7521, comes after siblings 'JAMES', 'MARTIN' and 'TURNER', even though 'WARD' has a lower empno.)

Hi,

3428354 wrote:
...
But, if I try and add an ORDER BY statement above the UNION ALL statement I get some sort of invalid SQL syntax.
...

The reason for that is: If you use ORDER BY on a set operation  (UNION, INTERSECT or MINUS), then the ORDER BY clause applies to the whole result set, and it is located after the last branch, at the very end of the query.  ORDER BY right before UNION is always a syntax error.

It rarely does any good to use ORDER BY on the results set of a sub-query, anyway.  If you're ever tempted to use an ORDER BY clause in a sub-query, there's a good chance what you really want is an analytic function (such as ROW_NUMBER) with ORDER BY applied to the function, not to the result set.

In your solution, there is no point in using ROW_NUMBER() in the anchor leg. Just letting   ordr_1   =   empno    (rather than row_number() ordered by empno) does exactly the same thing.

More importantly, I believe you missed a critical part of the original post:

How do you work around that, so the data in the end is sorted alphabetically by name, at each level of depth in the hierarchy?

The OP wants to order by name at ALL levels, not just at the first level.

Hi,

mathguy wrote:
In your solution, there is no point in using ROW_NUMBER() in the anchor leg. Just letting ordr_1 = empno (rather than row_number() ordered by empno) does exactly the same thing.
More importantly, I believe you missed a critical part of the original post:
How do you work around that, so the data in the end is sorted alphabetically by name, at each level of depth in the hierarchy?
The OP wants to order by name at ALL levels, not just at the first level.

It's unclear what OP wants.  The part you quoted contradicts what OP said just a couple of lines earlier

Now I would like to effectively ORDER BY  J1.JOBMST_NAME for the the anchor rows (the top level hierarchy J1 entries in my SQL, with NULL parents)

This is an example of why it's so important to post a little sample data, the exact results wanted from that sample data, and an explanation of how that data produces those results.

You are indeed correct.

I copied Tim Hall's code and mapped it to my own without thinking about that part.

FYI: Much of my early career was spent doing AS/400 development. So doing ad-hoc queries with their WRKQRY utility is something which is second nature to me, so creating temp tables that are ordered how you want them, then joining back to them is I guess my default modus operandi. The fact Oracle makes this so awkward is something I find somewhat excruciating. I posted about it here on Stack Overflow. Happily @mathguy simplified matters a great deal with his keen eye. :-)