Md. Shamvil Hossain Md. Shamvil Hossain - 3 months ago 10
MySQL Question

Mysql query to get latest data using date

enter image description here

This is my table. Now i have to find out the latest company_name of each user_id. The fact is that, if exp_to is null then it will be considered latest, or if both exp_from & exp_to are not null then i have to find the latest company name using exp_to date. Note that, user_id is appearing multiple times in the given table.

Here is what i want:

id | company_name
-----------------
14 | Deltalife Insurance Company Ltd.
1 | Orbital Software
25 | MMTV
18 | Noakhali Science & Technology University


and so on.....
How can i do it with mysql? Thanks in advance.

Answer

This is pretty straightforward aggregation problem. As you have likely figured out, nulls cause some interesting behavior in SQL. You can use CASE WHEN to check for null and replace with a different value.

In your case:

SELECT 
  data.* 
FROM (
  SELECT 
    *,
    CASE WHEN exp_to is null THEN 1 ELSE exp_to END max_date
  FROM your_table
) data
INNER JOIN (
  SELECT
    user_id,
    CASE WHEN MAX(exp_to is null) = 1 THEN 1 ELSE MAX(exp_to) END max_date
  FROM
    your_table
  GROUP BY user_id
) j
ON data.user_id = j.user_id AND data.max_date = j.max_date

Hope this helps, and good luck!