Oracle queries departments with the minimum number of employees

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.