Oracle 11 - GROUP BY in a LEFT JOIN not working

Question

I have a list of 3 IDs - 110098016, 110098017 and 110098021. All three of IDs are linked to the same Order (wiporderno).

I'm trying to place the ID that's sequentially less to it beside it in another column.

Oracle 11.2.0.4.0 (updating is sadly not possible)

The following segment of code runs and...

SELECT 
    ILV_L.ID
    ,ILVP.ID
    ,ILVP.rn
    ,ILV_L.wiporderno
FROM ILV_Labor ILV_L
LEFT JOIN 
        (SELECT 
            p.ID
            ,p.wiporderno
            ,MAX(p.rn) rn
        FROM
            (SELECT 
                ILVPP.ID
                ,ILVPP.wiporderno
                ,RANK() OVER (PARTITION BY ILVPP.wiporderno ORDER BY ILVPP.ID ASC) AS rn
            FROM ILV_Labor ILVPP) p
        GROUP BY id, wiporderno) ILVP       
    ON ILVP.ID < ILV_L.ID
        AND ILVP.wiporderno = ILV_L.wiporderno
        --AND (h.rn = 1 OR h.rn IS NULL)

... returns this.

For some unknown reason, the GROUP BY & MAX statement will not work. Grouping by the max should have filtered out where 110098021 is matched to 110098016 (i.e. where rn = 1 for 110098021). The following statement without the GROUP BY returns the exact same result.

SELECT 
    ILV_L.ID
    ,ILVP.ID
    ,ILVP.rn
    ,ILV_L.wiporderno
FROM ILV_Labor ILV_L
LEFT JOIN 
        (SELECT 
                ILVPP.ID
                ,ILVPP.wiporderno
                ,RANK() OVER (PARTITION BY ILVPP.wiporderno ORDER BY ILVPP.ID ASC) AS rn
            FROM ILV_Labor ILVPP) ILVP       
    ON ILVP.ID < ILV_L.ID
        AND ILVP.wiporderno = ILV_L.wiporderno

Is there something happening, potentially with the compiler, that prevents this GROUP BY from working?

Answer 1

Your GROUP BY is void. If you group by the table's ID, then you get one result row per ID. This is the same rows as in the original table, because the ID is unique. No aggregations takes place really.

If there is just one row per order in the table as in the sample data you have shown us, and you want the previous order, use LAG.

select l.*, lag(wiporderno) over (order by id) as previous_wiporderno
from ilv_labor l
order by id;