title: SQL query _ not able to find recently hired employee is each department
body: <p>I am trying to write a SQL SELECT statement to find out the most recently hired employees in EACH department. I am working on Oracle database and am trying to display the department name, employee name, hire date, and salary. The table definition for employee and department tables are below
  Department Table</p>
  
  <pre><code>CREATE TABLE dpt
  ( DEPARTMENT_ID         NUMBER(4) PRIMARY KEY,
    DEPARTMENT_NAME   VARCHAR2(20) NOT NULL,
    ADDRESS           VARCHAR2(20) NOT NULL);
  </code></pre>
  
  <p>Example Data</p>
  
  <pre><code>(10, 'ACCOUNTING', 'NEW YORK');
  (20, 'RESEARCH', 'DALLAS');
  (30, 'SALES', 'CHICAGO');
  </code></pre>
  
  <p>Employee Table</p>
  
  <pre><code>CREATE TABLE emp 
  ( EMPLOYEE_ID   NUMBER(4) PRIMARY KEY,
    EMPLOYEE_NAME VARCHAR2(20) NOT NULL,
    JOB           VARCHAR2(50) NOT NULL,
    MANAGER_ID    NUMBER(4),HIRE_DATE     DATE,
    SALARY        NUMBER(9, 2),
    COMMISSION    NUMBER(9, 2),
    DEPARTMENT_ID NUMBER(4) REFERENCES dpt(DEPARTMENT_ID));
  </code></pre>
  
  <p>Example Data</p>
  
  <pre><code> (7839, 'KING',  'PRESIDENT', NULL, '20-NOV-01', 5000, NULL, 50);
   (7596, 'JOST',  'VICE PRESIDENT', 7839, '04-MAY-01', 4500, NULL, 50);
   (7603, 'CLARK', 'VICE PRESIDENT', 7839, '12-JUN-01', 4000, NULL, 50);
  </code></pre>
  
  <p>The Query that I have written is outputting the most hired employee only in one dept. Can someone please point out what am I doing wrong? I tried using Group By but am not able to do it the right way.</p>
  
  <pre><code>SELECT dpt.department_name, dpt.department_id, employee_name, hire_date, Salary 
      from dpt, emp
      where emp.department_id = dpt.department_id
      and
      hire_date = (select max(hire_date) from emp where department_id = emp.department_id)
      order by dpt.department_name;
  </code></pre>