[sqlite] Equivalent Syntax for Oracle row_number() OVER (PARTITION)

[Igor Tandetnik]

On 9/11/2015 6:51 PM, Rousselot, Richard A wrote:
> What is the equivalent SQLite syntax for the Oracle SQL syntax below?
>
>                  row_number() OVER (PARTITION BY x ORDER BY y DESC, z) AS 
> aField
>
>                  Example...
>
> SELECT department_id, first_name, last_name, salary
> FROM
> (
>    SELECT
>      department_id, first_name, last_name, salary,
>      ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary desc) rn
>    FROM employees
> )
> WHERE rn <= 3
> ORDER BY department_id, salary DESC, last_name;

I'm not familiar with Oracle analytic functions. Going by the 
description of "finds the three highest paid employees in each department":

SELECT department_id, first_name, last_name, salary
FROM employees t1
WHERE rowid in (
   select rowid from employees t2
   where t2.department_id = t1.department_id
   order by t2.salary desc limit 3
)
ORDER BY department_id, salary DESC, last_name;

-- 
Igor Tandetnik