Skip to content
Advertisement

max, over partition by, oracle, take max rownum record

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;
8 People found this is helpful
Advertisement