ROW_NUMBER() SUB QUERY ISSUE

Question

I am trying to find activity during a specific time period.

there can and will be multiple records for one 'fileno' I want to limit that to one record.

SELECT   MASTER.FILENO, MASFORW.FORW_NO, MASTER.FORW_FILENO, 
         MASTER.COCO_FILENO, MASFORW.SORT_FIELD, TRACKUSR.trck_date, 
         TRACKUSR.whoami, TRACKUSR.track, TRACKUSR.rerite, 
         ROW_NUMBER() OVER (PARTITION BY  MASTER.FILENO  
                            ORDER BY TRACKUSR.TRCK_DATE DESC) AS rownum
FROM    MASFORW 
INNER JOIN  MASTER 
        ON MASFORW.FORW_NO = MASTER.FORW_NO 
FULL OUTER JOIN TRACKUSR 
        ON MASTER.FILENO = TRACKUSR.fileno
WHERE (TRACKUSR.TRCK_DATE >= DATEADD(day, 0, GETDATE())) 
  AND (TRACKUSR.TRCK_DATE < DATEADD(day, 90, GETDATE())) 
  AND (MASFORW.SORT_FIELD = N'CR' OR
                         MASFORW.SORT_FIELD = N'CJ') 
  and (rownum = 1)

sorry, error im getting is Msg 207, Level 16, State 1, Line 7 Invalid column name 'rownum'. — Patrick Lausen, Jan 25 '16 at 23:55

score 0 · Answer 1 · answered Jan 26 '16 at 00:17

create a sub query and then filter rownum outside.

You cant use alias on the same query because at that moment you check the where the alias hasnt be created

SELECT *
FROM (
      SELECT ROW_NUMBER() OVER (PARTITION BY  MASTER.FILENO  
                                ORDER BY TRACKUSR.TRCK_DATE DESC) AS rownum
      FROM ...
     ) as subquery
WHERE rownum = 1

score 0 · Answer 2 · edited May 23 '17 at 12:15

0

you can't use an alias at the where clause...

there is a sql statement hierarchy..

from table
where clause
group by
having
select
order by

related link

edited May 23 '17 at 12:15

Community

1
1

answered Jan 26 '16 at 00:33

RoMEoMusTDiE

4,739
1
17
26

score 0 · Answer 3 · answered Jan 26 '16 at 00:48

0

Assuming the rest of the query is ok, you can clear the error by replacing

and (rownum = 1)

with

having ROW_NUMBER() OVER (PARTITION BY  MASTER.FILENO  
       ORDER BY TRACKUSR.TRCK_DATE DESC) = 1

answered Jan 26 '16 at 00:48

Dan Bracuk

20,699
4
26
43

It seems that `Windowed functions can only appear in the SELECT or ORDER BY clauses.` – Eric Jan 26 '16 at 01:43

ROW_NUMBER() SUB QUERY ISSUE

3 Answers3