Oracle queries departments with the minimum number of employees

Source: Internet
Author: User


Oracle queries the department with the minimum number of employees [SQL] -- department table create table t_dept (d_id number primary key, d_name varchar2 (20); create sequence dept_sequence start with 1 increment by 1; www.2cto.com -- employee table create table t_emp (e_id number primary key, d_id number references t_dept (d_id), e_name varchar2 (20); create sequence emp_sequence start with 100 increment by 1; insert into t_dept values (dept_sequence.nextval, 'one'); insert into t_dept values (dept_sequence.nextval, 'biobao'); select * from t_dept;
-- Insert "one" into four employees: insert into t_emp values (emp_sequence.nextval, 2, 'zhang yi'); insert into t_emp values (emp_sequence.nextval, 2, 'zhang 2 '); insert into t_emp values (emp_sequence.nextval, 2, 'zhang san'); insert into t_emp values (emp_sequence.nextval, 2, 'zhang si '); -- insert two employees in Department 2: insert into t_emp values (emp_sequence.nextval, 3, 'Li yi'); insert into t_emp values (emp_sequence.nextval, 3, 'Li yi '); select * from t_emp; objective: to query the knowledge points of the Department with the least number of employees: grouping function, sorting, subquery, and rownumsql statement: [SQL] select * from (select count (*) coun, d_id deptId from t_emp group by d_id order by coun asc) where rownum = 1 www.2cto.com analysis: Step 1: select count (*) coun, d_id deptId from t_emp group by d_id query t_emp table, query the number of people in each department by the Department d_id group. Step 2: select count (*) coun, d_id deptId from t_emp group by d_id order by coun asc and then orderby coun asc. In this way, sort by number in ascending order. In this way, the first step is the step 3 with the least number of people: subquery, select * from (select count (*) coun, d_id deptId from t_emp group by d_id order by coun asc) where rownum = 1, because oracle does not support select top 1, when you want to obtain the first data, use the rownum column allocated to the query by using the kernel El. If where rownum = 1, the first data is obtained. The result is as follows: --------------------------- counn deptid limit 2 3.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.