ROW_NUMBER() fails using DATEDIFF output as ordering

Asked Dec 21 '18 at 17:59

Active Dec 21 '18 at 18:14

Viewed 25 times

Can anyone tell me why this throws an error?

WITH date_diffs AS 
(
   SELECT
       ctd_item_id,
       ctd_assigned_to,
       DATEDIFF(d, ctd_assigned_date, ctd_review_completed_date) AS DATE_DIFF,
       ROW_NUMBER() OVER (PARTITION BY ctd_item_id ORDER BY date_diff DESC) AS RN 
   FROM
       irt_item_tracking_detail_tbl
)
SELECT
    * 
FROM
    date_diffs 
WHERE
    date_diffs.rn = 1

Error is

Invalid column name 'date_diff'

Can it not be created within the SELECT statement in the WITH?

edited Dec 21 '18 at 18:14

marc_s

732,580
175
1,330
1,459

asked Dec 21 '18 at 17:59

traggatmot

1,423
5
26
51

2

you can't reference aliases created in the same logical processing level – Martin Smith Dec 21 '18 at 18:01
martin is right, solution is to create an outer loop to have your row_number() added – Maria Nazari Dec 21 '18 at 18:45

ROW_NUMBER() fails using DATEDIFF output as ordering

0 Answers0