Issue in SQL query full scanning twice?

Table A

ID    EmpNo   开发者_开发百科Grade
--------------------    
1      100    HIGH
2      105    LOW
3      100    MEDIUM
4      100    LOW
5      105    LOW

Query:

select * 
from A 
where EMPNO = 100 
  and rownum <= 2 
order by ID desc 

I tried this query to retrieve max and max-1 value; I need to compare the grade from max and max-1, if equals I need to set the flag as 'Y' or 'N' without using a cursor. Also I don't want to scan the entire record twice.

Please help me.

ROWNUM is applied before ORDER BY, so you need to nest the query like this:

select * from
(select * from A where EMPNO =100 order by ID desc)
where rownum<=2

That only performs one table scan (or it may use an index on EMPNO).

select *
from (
select id, emp_no, grade
       , case 
          when lag(grade) over (order by emp_no desc) = grade 
          then 'Y' 
          else 'N'     
          end
          as flag
      , dense_rank() over( order by emp_no desc)  as rank
from t
)
where rank <=2
;