Hi,
I have created a table in Mysql emp_sample & Salary_sample
I have 1001 to 1010 employee records and loaded in emp_sample
emp_sample:
mysql> select * from emp_sample;
+——–+————+————+————-+——–+————+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+——–+————+————+————-+——–+————+
| 10001 | 02-09-1953 | Georgi | Facello | M | 26-06-1986 |
| 10002 | 02-06-1964 | Bezalel | Simmel | F | 21-11-1985 |
| 10003 | 03-12-1959 | Parto | Bamford | M | 28-08-1986 |
| 10004 | 01-05-1954 | Chirstian | Koblick | M | 01-12-1986 |
| 10005 | 21-01-1955 | Kyoichi | Maliniak | M | 12-09-1989 |
| 10006 | 20-04-1953 | Anneke | Preusig | F | 02-06-1989 |
| 10007 | 23-05-1957 | Tzvetan | Zielinski | F | 10-02-1989 |
| 10008 | 19-02-1958 | Saniya | Kalloufi | M | 15-09-1994 |
| 10009 | 19-04-1952 | Sumant | Peac | F | 18-02-1985 |
| 10010 | 01-06-1963 | Duangkaew | Piveteau | F | 24-08-1989 |
Each employee have multiple salary based on their appraisal from date range (from_date , to date). see below table
mysql> select * from salary_sample;
+——–+——–+————+————+
| emp_no | salary | from_date | to_date |
+——–+——–+————+————+
| 10001 | 60117 | 26-06-1986 | 26-06-1987 |
| 10001 | 62102 | 26-06-1987 | 25-06-1988 |
| 10001 | 66074 | 25-06-1988 | 25-06-1989 |
| 10001 | 66596 | 25-06-1989 | 25-06-1990 |
| 10001 | 66961 | 25-06-1990 | 25-06-1991 |
| 10001 | 71046 | 25-06-1991 | 24-06-1992 |
| 10001 | 74333 | 24-06-1992 | 24-06-1993 |
| 10001 | 75286 | 24-06-1993 | 24-06-1994 |
| 10001 | 75994 | 24-06-1994 | 24-06-1995 |
| 10001 | 76884 | 24-06-1995 | 23-06-1996 |
| 10001 | 80013 | 23-06-1996 | 23-06-1997 |
| 10001 | 81025 | 23-06-1997 | 23-06-1998 |
| 10001 | 81097 | 23-06-1998 | 23-06-1999 |
| 10001 | 84917 | 23-06-1999 | 22-06-2000 |
| 10001 | 85112 | 22-06-2000 | 22-06-2001 |
| 10001 | 85097 | 22-06-2001 | 22-06-2002 |
| 10001 | 88958 | 22-06-2002 | 01-01-9999 |
| 10002 | 65828 | 03-08-1996 | 03-08-1997 |
| 10002 | 65909 | 03-08-1997 | 03-08-1998 |
| 10002 | 67534 | 03-08-1998 | 03-08-1999 |
| 10002 | 69366 | 03-08-1999 | 02-08-2000 |
| 10002 | 71963 | 02-08-2000 | 02-08-2001 |
| 10002 | 72527 | 02-08-2001 | 01-01-9999 |
| 10003 | 40006 | 03-12-1995 | 02-12-1996 |
| 10003 | 43616 | 02-12-1996 | 02-12-1997 |
| 10003 | 43466 | 02-12-1997 | 02-12-1998 |
| 10003 | 43636 | 02-12-1998 | 02-12-1999 |
| 10003 | 43478 | 02-12-1999 | 01-12-2000 |
| 10003 | 43699 | 01-12-2000 | 01-12-2001 |
| 10003 | 43311 | 01-12-2001 | 01-01-9999 |
| 10004 | 40054 | 01-12-1986 | 01-12-1987 |
| 10004 | 42283 | 01-12-1987 | 30-11-1988 |
| 10004 | 42542 | 30-11-1988 | 30-11-1989 |
| 10004 | 46065 | 30-11-1989 | 30-11-1990 |
| 10004 | 48271 | 30-11-1990 | 30-11-1991 |
| 10004 | 50594 | 30-11-1991 | 29-11-1992 |
| 10004 | 52119 | 29-11-1992 | 29-11-1993 |
| 10004 | 54693 | 29-11-1993 | 29-11-1994 |
| 10004 | 58326 | 29-11-1994 | 29-11-1995 |
| 10004 | 60770 | 29-11-1995 | 28-11-1996 |
| 10004 | 62566 | 28-11-1996 | 28-11-1997 |
| 10004 | 64340 | 28-11-1997 | 28-11-1998 |
| 10004 | 67096 | 28-11-1998 | 28-11-1999 |
| 10004 | 69722 | 28-11-1999 | 27-11-2000 |
| 10004 | 70698 | 27-11-2000 | 27-11-2001 |
| 10004 | 74057 | 27-11-2001 | 01-01-9999 |
| 10005 | 78228 | 12-09-1989 | 12-09-1990 |
| 10005 | 82621 | 12-09-1990 | 12-09-1991 |
| 10005 | 83735 | 12-09-1991 | 11-09-1992 |
| 10005 | 85572 | 11-09-1992 | 11-09-1993 |
| 10005 | 85076 | 11-09-1993 | 11-09-1994 |
| 10005 | 86050 | 11-09-1994 | 11-09-1995 |
| 10005 | 88448 | 11-09-1995 | 10-09-1996 |
| 10005 | 88063 | 10-09-1996 | 10-09-1997 |
| 10005 | 89724 | 10-09-1997 | 10-09-1998 |
| 10005 | 90392 | 10-09-1998 | 10-09-1999 |
| 10005 | 90531 | 10-09-1999 | 09-09-2000 |
| 10005 | 91453 | 09-09-2000 | 09-09-2001 |
| 10005 | 94692 | 09-09-2001 | 01-01-9999 |
| 10006 | 40000 | 05-08-1990 | 05-08-1991 |
| 10006 | 42085 | 05-08-1991 | 04-08-1992 |
| 10006 | 42629 | 04-08-1992 | 04-08-1993 |
| 10006 | 45844 | 04-08-1993 | 04-08-1994 |
| 10006 | 47518 | 04-08-1994 | 04-08-1995 |
| 10006 | 47917 | 04-08-1995 | 03-08-1996 |
| 10006 | 52255 | 03-08-1996 | 03-08-1997 |
| 10006 | 53747 | 03-08-1997 | 03-08-1998 |
| 10006 | 56032 | 03-08-1998 | 03-08-1999 |
| 10006 | 58299 | 03-08-1999 | 02-08-2000 |
| 10006 | 60098 | 02-08-2000 | 02-08-2001 |
| 10006 | 59755 | 02-08-2001 | 01-01-9999 |
| 10007 | 56724 | 10-02-1989 | 10-02-1990 |
| 10007 | 60740 | 10-02-1990 | 10-02-1991 |
| 10007 | 62745 | 10-02-1991 | 10-02-1992 |
| 10007 | 63475 | 10-02-1992 | 09-02-1993 |
| 10007 | 63208 | 09-02-1993 | 09-02-1994 |
| 10007 | 64563 | 09-02-1994 | 09-02-1995 |
| 10007 | 68833 | 09-02-1995 | 09-02-1996 |
| 10007 | 70220 | 09-02-1996 | 08-02-1997 |
| 10007 | 73362 | 08-02-1997 | 08-02-1998 |
| 10007 | 75582 | 08-02-1998 | 08-02-1999 |
| 10007 | 79513 | 08-02-1999 | 08-02-2000 |
| 10007 | 80083 | 08-02-2000 | 07-02-2001 |
| 10007 | 84456 | 07-02-2001 | 07-02-2002 |
| 10007 | 88070 | 07-02-2002 | 01-01-9999 |
| 10008 | 46671 | 11-03-1998 | 11-03-1999 |
| 10008 | 48584 | 11-03-1999 | 10-03-2000 |
| 10008 | 52668 | 10-03-2000 | 31-07-2000 |
| 10009 | 60929 | 18-02-1985 | 18-02-1986 |
| 10009 | 64604 | 18-02-1986 | 18-02-1987 |
| 10009 | 64780 | 18-02-1987 | 18-02-1988 |
| 10009 | 66302 | 18-02-1988 | 17-02-1989 |
| 10009 | 69042 | 17-02-1989 | 17-02-1990 |
| 10009 | 70889 | 17-02-1990 | 17-02-1991 |
| 10009 | 71434 | 17-02-1991 | 17-02-1992 |
| 10009 | 74612 | 17-02-1992 | 16-02-1993 |
| 10009 | 76518 | 16-02-1993 | 16-02-1994 |
| 10009 | 78335 | 16-02-1994 | 16-02-1995 |
| 10009 | 80944 | 16-02-1995 | 16-02-1996 |
| 10009 | 82507 | 16-02-1996 | 15-02-1997 |
| 10009 | 85875 | 15-02-1997 | 15-02-1998 |
| 10009 | 89324 | 15-02-1998 | 15-02-1999 |
| 10009 | 90668 | 15-02-1999 | 15-02-2000 |
| 10009 | 93507 | 15-02-2000 | 14-02-2001 |
| 10009 | 94443 | 14-02-2001 | 14-02-2002 |
| 10009 | 94409 | 14-02-2002 | 01-01-9999 |
| 10010 | 72488 | 24-11-1996 | 24-11-1997 |
| 10010 | 74347 | 24-11-1997 | 24-11-1998 |
| 10010 | 75405 | 24-11-1998 | 24-11-1999 |
| 10010 | 78194 | 24-11-1999 | 23-11-2000 |
| 10010 | 79580 | 23-11-2000 | 23-11-2001 |
| 10010 | 80324 | 23-11-2001 | 01-01-9999 |
I have provided the below query:
- Joined both the table and bring emp_sample and salary_smple details together
- During the join one latest record of salary_sample of each emp_no to be joined (I mean only one latest salary records to be obtained for each emp_no and joined with emp_sample table)
mysql> select emp_sample.emp_no, birth_date, first_name, last_name, gender, hire_date, salary, from_date, to_date from emp_sample join salary_sample on emp_sample.emp_no=salary_sample.emp_no where salary in (select max(salary) from salary_sample group by emp_no);
While provide the above query i found 6th record duplicates see the below details. (Duplicate means minimum salary also present. )
I need only one latest salary records to be obtained for each emp_no.
mysql> select emp_sample.emp_no, birth_date, first_name, last_name, gender, hire_date, salary, from_date, to_date from emp_sample join salary_sample on emp_sample.emp_no=salary_sample.emp_no where salary in (select max(salary) from salary_sample group by emp_no);
+——–+————+————+————-+——–+————+——–+————+————+
| emp_no | birth_date | first_name | last_name | gender | hire_date | salary | from_date | to_date |
+——–+————+————+————-+——–+————+——–+————+————+
| 10001 | 02-09-1953 | Georgi | Facello | M | 26-06-1986 | 88958 | 22-06-2002 | 01-01-9999 |
| 10002 | 02-06-1964 | Bezalel | Simmel | F | 21-11-1985 | 72527 | 02-08-2001 | 01-01-9999 |
| 10003 | 03-12-1959 | Parto | Bamford | M | 28-08-1986 | 43699 | 01-12-2000 | 01-12-2001 |
| 10004 | 01-05-1954 | Chirstian | Koblick | M | 01-12-1986 | 74057 | 27-11-2001 | 01-01-9999 |
| 10005 | 21-01-1955 | Kyoichi | Maliniak | M | 12-09-1989 | 94692 | 09-09-2001 | 01-01-9999 |
| 10006 | 20-04-1953 | Anneke | Preusig | F | 02-06-1989 | 40000 | 05-08-1990 | 05-08-1991 |
| 10006 | 20-04-1953 | Anneke | Preusig | F | 02-06-1989 | 60098 | 02-08-2000 | 02-08-2001 |
| 10007 | 23-05-1957 | Tzvetan | Zielinski | F | 10-02-1989 | 88070 | 07-02-2002 | 01-01-9999 |
| 10008 | 19-02-1958 | Saniya | Kalloufi | M | 15-09-1994 | 52668 | 10-03-2000 | 31-07-2000 |
| 10009 | 19-04-1952 | Sumant | Peac | F | 18-02-1985 | 94443 | 14-02-2001 | 14-02-2002 |
| 10010 | 01-06-1963 | Duangkaew | Piveteau | F | 24-08-1989 | 80324 | 23-11-2001 | 01-01-9999 |
Kindly check and do the needful.