Evaluate WHERE predicates on analytic functions before other predicates (Oracle analytic functions)

Question

Background

Sample data set

#Employee
Id | Period | Status 
---------------------
1  |  1 |   L    
1  |  2 |   G    
2  |  3 |   L    

I want a simple select query to yield employees' latest record (by period) only if the status='L'.

The results would look like this:

#Desired Results
Id | Period | Status | Sequence
-------------------------------
2  |  3     |   L    |   1

Naive attempt

Obviously, my naive attempt at a query does not work:

#select query
SELECT *, RANK() OVER (PARTITION BY id ORDER BY period ASC) sequence
FROM employees
WHERE   status = 'L' 
 AND    sequence = 1

Which results in the following:

#Naive (incorrect) Results
ID | Period | Status | Sequence
-------------------------------
1  |  1 |   L    |   1
2  |  3 |   L    |   1

Knowing the order that clauses are evaluated in SQL explains why it doesn't work. Here is how my query is evaluated:

• Isolate rows where status='L'
• Rank the rows
• Isolate top rank row

I want the following:

• Rank rows
• Isolate the top ranked rows
• Isolate where status='L'

Questions

• Is possible--with only a simple modification to the SELECT/WHERE clauses and using only basic predicate operators--to ensure that predicates based on analytic functions in the WHERE clause get evaluated before the non-aggregate predicates?

• Anyone have other solutions that can be implemented as an end-user in Oracle Discoverer Plus?

Thanks!

Answer 1

Is it possible to do this without a sub-query

Technically the following is not a sub-query but a derived table

SELECT * 
FROM (
    SELECT *, 
           RANK() OVER (PARTITION BY id ORDER BY period ASC) sequence
    FROM employees
) t
WHERE status = 'L' 
  AND sequence = 1

I can't think of a different solution to your problem.