mysql - Sql query to print all Department Names and the name of the newest employee in that department

Question

Am Kind of new to MySql and am trying to retrieve a query but got struck for some time.

Sample data set:

   CREATE TABLE `employee` (
  `EmpId` int(11) NOT NULL,
  `EmpName` varchar(100) NOT NULL,
  `DeptId` int(11) NOT NULL,
  `Jod` date NOT NULL,
  `Salary` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



INSERT INTO `employee` (`EmpId`, `EmpName`, `DeptId`, `Jod`, `Salary`) VALUES
(1, 'ABCD', 1, '2015-02-23', 50000),
(2, 'EFGH', 1, '2016-04-11', 40000),
(3, 'HIJK', 2, '2016-05-22', 35000),
(4, 'LMNO', 3, '2016-05-22', 30000),
(5, 'PQRS', 3, '2016-06-03', 30000);

CREATE TABLE `dept` (
  `DeptId` int(11) DEFAULT NULL,
  `DeptName` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



INSERT INTO `dept` (`DeptId`, `DeptName`) VALUES
(1, 'Sales'),
(2, 'Account'),
(3, 'Support');

DB Fiddle

where i am trying to execute the queries.

Now I need a query to print all Department Names and the name of the newest employee in that department

So as per my understanding the desired result must be

 Dept Name         Employee Name
 ---------         -------------

 Sales              EFGH
 Account            HIJK
 Support            PQRS

I have already done some query ,The link is attached above . DB Fiddle

How to get the desired result,Any help aprreciated.

question from:https://stackoverflow.com/questions/65896841/sql-query-to-print-all-department-names-and-the-name-of-the-newest-employee-in-t

Answer 1

On MySQL 8+ using ROW_NUMBER, we can try:

WITH cte AS (
    SELECT d.DeptName, e.EmpName,
           ROW_NUMBER() OVER (PARTITION BY d.DeptId ORDER BY e.Jod DESC) rn
    FROM dept d
    INNER JOIN employee e ON e.DeptId = d.DeptId
)

SELECT DeptName, EmpName
FROM cte
WHERE rn = 1;

Demo