CODEWITHSUNDEEP
sqloracle-database
devcoder112
devcoder112

Reputation: 161

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

Upvotes: 1

Views: 1337

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

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;

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I think the problem with ordering you need desc : 

SELECT a.*
FROM (select nt.*
             ROW_NUMBER() OVER (PARTITION BY id ORDER BY rownum DESC) AS rownum1
      FROM name_table nt
      WHERE rownum <= 2000
     ) a
WHERE a.rownum1 = 1;

By this way you will get max rownum (assuming rownum has meaningful data).

Upvotes: 1

Related Questions