본문 바로가기
Dev./Back-end

/*Over(partition by)*/ /*inner query*/

by 돌도르 2012. 9. 24.
728x90

/*Over(partition by)*/

        select employee_id, last_name, department_name, salary

from (    

        select employee_id, last_name,salary, department_name, max(salary) over (partition by d.department_id) as maxsal

        from employees e join departments d

        on e.department_id=d.department_id

      )

where maxsal=salary

order by employee_id asc;


/*inner query*/

select employee_id, last_name, department_name, salary

from employees e, departments d

where e.department_id=d.department_id 

and (d.department_id, salary) in (

                                    select department_id, max(salary)

                                    from employees

                                    group by department_id

                                  )

order by employee_id asc;




'Dev. > Back-end' 카테고리의 다른 글

20120925 Oracle  (0) 2012.09.25
Oracle 연습문제 . 데이터입력  (0) 2012.09.24
120911 오라클  (0) 2012.09.11
20120831 Oracle  (0) 2012.09.02
상태: 실패 -테스트 실패: ORA-28000: the account is locked  (0) 2012.09.02

댓글