rank() analytic function

Oracle rank

As an analytic function, RANK computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_exprs in the order_by_clause.

返回工资第二高的员工

SELECT * FROM (   
    SELECT employee_id, last_name, salary,   
        RANK() OVER (ORDER BY salary DESC) EMPRANK   
    FROM employees)
WHERE emprank = 2; 

verify result

SELECT employee_id, last_name, salary, RANK() OVER (ORDER BY salary DESC) EMPRANK FROM employees;   

另外一个例子

SELECT department_id,last_name,salary,commission_pct, 
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC, commission_pct RANK 
FROM employees;