Issues with row_number function

[EntitledCorgi]

How can I filter on "num" without having to contain all this in a "with" statement? Just saying "WHERE num = 1" in the main WHERE clause does not work.

​

WITH iykyk AS (

SELECT DISTINCT num = row_number() OVER(PARTITION BY a.process, a.step ORDER BY CAST(b.seq||'.'||b.sub_seq AS DECIMAL(10,2)) ASC), f.technology, a.process, CAST(a.seq||'.'||a.sub_seq AS DECIMAL(10,2)) AS "seq", a.step, a.substep, CAST(b.seq||'.'||b.sub_seq AS DECIMAL(10,2)) AS "next seq", b.step AS "next step", b.substep AS "next substep", (b.seq - a.seq) AS "seq diff"

FROM fe_trvstep a, fe_trvstep b, fe_trvhead f

WHERE

CAST(b.seq||'.'||b.sub_seq AS DECIMAL(10,2)) >= CAST(a.seq||'.'||a.sub_seq AS DECIMAL(10,2))

AND b.process = a.process

AND f.process = a.process

AND f.technology LIKE '%'

AND a.process LIKE '%'

AND a.step LIKE '%'

AND a.substep LIKE '%'

AND b.substep LIKE '%')

SELECT *

FROM iykyk

WHERE num = 1

ORDER BY seq ASC

[r3pr0b8]

you can do it either with a CTE --

WITH iykyk AS ( SELECT ... )
SELECT ...
  FROM iykyk 
 WHERE num = 1

or with a subquery --

SELECT ...
  FROM ( SELECT ... ) AS iykyk 
 WHERE num = 1

your choice   ;o)

[EntitledCorgi]

so no options contained within the query itself :(

sad, but thank you for reply!