Md. Shamvil Hossain Md. Shamvil Hossain - 1 year ago 111
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 Source

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:

    CASE WHEN exp_to is null THEN 1 ELSE exp_to END max_date
  FROM your_table
) data
    CASE WHEN MAX(exp_to is null) = 1 THEN 1 ELSE MAX(exp_to) END max_date
  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!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download