This shows you the differences between two versions of the page.
Both sides previous revision Previous revision | |||
kurs:rank [2014/09/10 21:22] 127.0.0.1 external edit |
kurs:rank [2024/03/05 15:29] (current) admin |
||
---|---|---|---|
Line 7: | Line 7: | ||
where emprank > 10 and emprank < 13 | where emprank > 10 and emprank < 13 | ||
-- WHERE emprank = 2; | -- WHERE emprank = 2; | ||
+ | </code> | ||
+ | |||
+ | <code> | ||
+ | with cte_emp_dep as ( | ||
+ | SELECT | ||
+ | emp.first_name, | ||
+ | emp.last_name, | ||
+ | emp.salary, | ||
+ | dep.department_name | ||
+ | FROM | ||
+ | employees emp left join departments dep | ||
+ | on emp.department_id = dep.department_id | ||
+ | where salary < 9000 | ||
+ | ), | ||
+ | cte_sal_bin as ( | ||
+ | select cte_emp_dep.*, | ||
+ | trunc(salary, -3) sal_bin | ||
+ | from cte_emp_dep | ||
+ | ), | ||
+ | cte_rank as ( | ||
+ | select cte_sal_bin.*, | ||
+ | dense_rank() over (order by sal_bin) rank_sal_bin | ||
+ | from cte_sal_bin | ||
+ | ) | ||
+ | select * from cte_rank | ||
+ | where rank_sal_bin < 10 | ||
+ | order by salary | ||
</code> | </code> |