How to shorten (replace) query of 2 CTE into 1 JOIN LATERAL

Hmmm . . . I think you want:

select t.*
from (select, t.platform_order_number, v.platform,
             row_number() over (partition by v.platform order by t.platform_order_number) as seqnum
       from table t cross apply lateral
            (values (case when platform_order_number LIKE 'B2C%' then 'B2C'
                          when platform_order_number LIKE 'B2B%' then 'C2C'
                          else 'C2C'
            ) v(platform)
      ) t;

In general, window functions may not behave as expected in a lateral join. They only work on the rows processed in the lateral join (often just one row) rather than spanning the original table.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top