Question 21066 in Mysql Question posted in
The official MySQL documentation can be found here.

Query the Latest Data Based On Condition in Multiple Columns and Concat Them – Mysql

I have a database like this

employees

I try to query the latest job and salary for every employees based on latest s_from_date and t_from_date and concat it to one column "Current Employees"

I try to filter it with max(s_from_date) and max(t_from_date) but it didn’t work

SELECT CONCAT (first_name,' ',last_name,' ',salary,' ',title) As "Current Employees" 
FROM employees WHERE s_from_date = (SELECT MAX(s_from_date) FROM employees) AND t_from_date = (SELECT MAX(t_from_date) FROM employees);
Tags:

2

Answers


  1. 0

    if you use phpmyadmin, it can tell where the error position, please see image bellow.

    enter image description here

    I think you missed "(" after "concat".

    SELECT CONCAT(first_name,' ',last_name,' ',salary,' ',title) As Current Employees
FROM employees WHERE s_from_date = (SELECT MAX(from_date) FROM employees) AND t_from_date = (SELECT MAX(from_date) FROM employees)
    Login or Signup to reply.
  2. 0

    You could try

    SELECT CONCAT(first_name,' ',last_name,' ',salary,' ',title) As "Current Employees" 
FROM employees e1
WHERE s_from_date = (SELECT MAX(s_from_date) FROM employees e2
                    WHERE e2.first_name = e1.first_name AND e2.last_name = e1.last_name) 
     AND t_from_date = (SELECT MAX(t_from_date) FROM employees e3
                    WHERE e3.first_name = e1.first_name AND e3.last_name = e1.last_name);

    If your employees table has id/employee_id column, instead of using last_name and first_name in condition of 2 subqueries, you could change it to e2.id = e1.id, e3.id = e1.id.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search