Question :
Can I write MySQL query to identify employees who earn less than employees hired after them.
employees:
+--------+----------+----------+------+-------------+------+
| emp_id | emp_name | job | mgr | hire_date | sal |
+--------+----------+----------+------+-------------+------+
| 7369 | Smith | Clerk | 7902 | 17-Dec-2010 | 1800 |
| 7499 | Allen | Salesman | 7698 | 20-Feb-2011 | 2600 |
| 7521 | Ward | Salesman | 7698 | 22-Feb-2011 | 2250 |
| 7566 | Jones | Manager | 7839 | 02-Apr-2011 | 3975 |
| 7654 | Martin | Salesman | 7698 | 28-Sep-2011 | 2250 |
| 7698 | Blake | Manager | 7839 | 01-May-2011 | 3850 |
| 7782 | Clark | Manager | 7839 | 09-Jun-2011 | 3450 |
| 7788 | Scott | Analyst | 7566 | 09-Dec-2012 | 4000 |
| 7839 | King | CEO | | 12-Nov-2011 | 6000 |
| 7844 | Turner | Salesman | 7698 | 08-Sep-2011 | 2500 |
| 7876 | Adams | Clerk | 7788 | 12-Jan-2013 | 2100 |
| 7900 | James | Clerk | 7698 | 03-Dec-2011 | 1950 |
| 7902 | Ford | Analyst | 7566 | 03-Dec-2011 | 4000 |
| 7934 | Miller | Clerk | 7782 | 23-Jan-2012 | 2300 |
+--------+----------+----------+------+-------------+------+
Expected output
+--------+----------+----------+------+-------------+
| emp_id | emp_name | job | hire_date | sal |
+--------+----------+----------+------+-------------+
| 7369 | Smith | Clerk | 17-Dec-2010 | 1800 |
| 7521 | Ward | Salesman | 22-Feb-2011 | 2250 |
| 7654 | Martin | Salesman | 28-Sep-2011 | 2250 |
| 7782 | Clark | Manager | 09-Jun-2011 | 3450 |
| 7788 | Scott | Analyst | 09-Dec-2012 | 4000 |
| 7839 | King | CEO | 12-Nov-2011 | 6000 |
| 7900 | James | Clerk | 03-Dec-2011 | 1950 |
+--------+----------+----------+------+-------------+
should return data in the format:
{
employeeId: xxx,
employeeName: xxx,
salary: xxx,
hireDate: xxx
}
Answer :
You can use a correlated subquery and GROUP_CONCAT() function to get the previous id.
select x.emp_id,
x.emp_name,
x.job,
x.mgr,
x.hire_date,
x.sal,
(select group_concat(y.emp_id)
from employees y
where y.hire_date < x.hire_date
and y.sal < x.sal
order by y.hire_date desc) prev_emp_id
from employees x
order by x.emp_id;
Rextester here
| | emp_id | emp_name | job | mgr | hire_date | sal | prev_emp_id |
|:--:|:------:|:--------:|----------|------|------------|------|---------------------------------------------------|
| 1 | 7369 | Smith | Clerk | 7902 | 17.12.2010 | 1800 | NULL |
| 2 | 7499 | Allen | Salesman | 7698 | 20.02.2011 | 2600 | 7369 |
| 3 | 7521 | Ward | Salesman | 7698 | 22.02.2011 | 2250 | 7369 |
| 4 | 7566 | Jones | Manager | 7839 | 02.04.2011 | 3975 | 7369,7499,7521 |
| 5 | 7654 | Martin | Salesman | 7698 | 28.09.2011 | 2250 | 7369 |
| 6 | 7698 | Blake | Manager | 7839 | 01.05.2011 | 3850 | 7369,7499,7521 |
| 7 | 7782 | Clark | Manager | 7839 | 09.06.2011 | 3450 | 7369,7499,7521 |
| 8 | 7788 | Scott | Analyst | 7566 | 09.12.2012 | 4000 | 7369,7499,7521,7566,7654,7698,7782,7844,7900,7934 |
| 9 | 7839 | King | CEO | 0 | 12.11.2011 | 6000 | 7369,7499,7521,7566,7654,7698,7782,7844 |
| 10 | 7844 | Turner | Salesman | 7698 | 08.08.2011 | 2500 | 7369,7521 |
| 11 | 7876 | Adams | Clerk | 7788 | 12.01.2013 | 2100 | 7369,7900 |
| 12 | 7900 | James | Clerk | 7698 | 03.12.2011 | 1950 | 7369 |
| 13 | 7902 | Ford | Analyst | 7566 | 03.12.2011 | 4000 | 7369,7499,7521,7566,7654,7698,7782,7844 |
| 14 | 7934 | Miller | Clerk | 7782 | 23.01.2012 | 2300 | 7369,7521,7654,7900 |