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
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!