CODEWITHSUNDEEP
mysqlsqldateaggregate-functions
Md. Shamvil Hossain
Md. Shamvil Hossain

Reputation: 424

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.

Upvotes: 0

Views: 47

Answers (3)

Nate Vaughan
Nate Vaughan

Reputation: 3839

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!

Upvotes: 1

O. Jones
O. Jones

Reputation: 108676

This is an application for the structured part of Structured query language.

First, you need to know which date is the latest for each user_id. This subquery yields that information:

       SELECT user_id,
              MAX(IFNULL(NOW(), exp_to) exp_to
         FROM mytable
        GROUP BY user_id

Then, you need to use that result to find the company, by joining it to your table.

SELECT a.user_id, a.company_name
  FROM mytable a
  JOIN (
            SELECT user_id,
                   MAX(IFNULL(NOW(), exp_to) exp_to
              FROM mytable
             GROUP BY user_id
       ) b ON a.user_id = b.user_id AND IFNULL(NOW(), a.exp_to) = b.exp_to

That should locate the company name associated with the most recent exp_to for each user_id. IFNULL(NOW(),exp_to) implements your requirement that a NULL date be considered as if it were the present time.

This query is an example of a general pattern of queries: Find the rows with extreme (max,min) values of a particular values.

Upvotes: 1

Nayan Srivastava
Nayan Srivastava

Reputation: 3725

With whatever information you have provided, I can figure-out this query

Select * from Table_Name where exp_to = null || exp_to = (select max(exp_to) from Table_Name)

Upvotes: 1

Related Questions