I am trying to pull the maximum rownum in a partition. I am getting the below error message, so I need help to fix my SQL Query. I added in a row number and a row number in a partition in my SQL query. Code is below. I want to take the maximum over this partition and have tried changing the query a few times, but keep getting error messages, so I am getting stuck. I need some help. Please see the below code and error message.
SELECT *, max(rownum1) OVER (PARTITION BY id) as maxrownum1 from ( select id, NAME_TYPE, NAME, EFFDT, rownum, ROW_NUMBER() OVER (PARTITION BY id ORDER BY rownum ASC) AS rownum1 FROM name_table ) a where a.rownum1=maxrownum1 and rownum<=2000
Error message
ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected" *Cause: *Action: Error at Line: 1 Column: 9
Advertisement
Answer
This seems very curious. You cannot refer to an alias in the where
clause for the select
that defines it. So, put the definition in a subquery:
select t.*, from (select id, NAME_TYPE, NAME, EFFDT, rownum as rn, max(rownum) over (partition by id) as max_rownum from name_table ) nt where rn <= max_rownum and rownum <= 2000;