MySQL – How to Query Closest Date and Display Specific Data

greatest-n-per-groupMySQL

guys, I have a problem displaying data from my query code.
I want to display data from the closest date today. Below are my sample data and my output data that I want to display.

Landing Area data.

|landing_id| id_number| address |
+----------+----------+---------+
|    1     | 00012345 | Ozamiz  |
|    2     | 00012346 | Tudela  |
|    3     | 00012347 | Nailon  |
|    4     | 00012348 | Taboo   |
|    5     | 00012349 | Jimenez |
|    6     | 00012350 | Tangub  |
+----------+----------+---------+

Percentage data.

|percent_id| landing_id | percentage |     date_added     |
+----------+------------+------------+--------------------+
|    1     |     1      |    19      |2018-10-16 21:42:22 |
|    2     |     1      |    44      |2018-10-16 20:43:32 |
|    3     |     5      |    13      |2018-10-15 19:43:49 |
|    4     |     3      |    22      |2018-10-13 15:43:56 |
|    5     |     3      |    54      |2018-10-12 18:44:03 |
+----------+------------+------------+--------------------+

The query code is.

SELECT fish_landing.landing_id, 
   percentage.percentage, 
   percentage.date_added, 
   fish_landing.address  
FROM fish_landing 
LEFT JOIN percentage ON percentage.landing_id = fish_landing.landing_id 
LEFT JOIN (SELECT landing_id, MAX(date_added) AS max_date_added 
        FROM percentage 
        GROUP BY landing_id) AS dt 
ON dt.landing_id = percentage.landing_id AND 
 dt.max_date_added = percentage.date_added
ORDER BY fish_landing.landing_id ASC

The output of the query code above is this, where the date_added and the percentage is not exact, because the other landing_id is displayed.

|landing_id| percentage |     date_added     |
+----------+------------+--------------------+
|    1     |    19      |2018-10-16 21:42:22 |
|    1     |    44      |2018-10-16 20:43:32 |
|    2     |            |                    |
|    3     |    54      |2018-10-12 18:44:03 |
|    3     |    22      |2018-10-13 15:43:56 |
|    4     |            |                    |
|    5     |    13      |2018-10-15 19:43:49 |
|    6     |            |                    |
+----------+------------+--------------------+

And the output data that I want to display is the table below, having a latest date_added in every landing_id will be displayed. 

|landing_id| percentage |     date_added     |
+----------+------------+--------------------+
|    1     |    19      |2018-10-16 21:42:22 |
|    2     |            |                    |
|    3     |    22      |2018-10-13 15:43:56 |
|    4     |            |                    |
|    5     |    13      |2018-10-15 19:43:49 |
|    6     |            |                    |
+----------+------------+--------------------+

I hope you can help me in my problem.

Best Answer

Try this trick

SELECT cte.landing_id, 
       cte.percentage, 
       cte.date_added, 
       cte.address
FROM (  SELECT fish_landing.landing_id, 
               percentage.percentage, 
               percentage.date_added, 
               fish_landing.address,
               (@row_number:=CASE WHEN @landing_id=fish_landing.landing_id THEN @row_number+1 ELSE 1 END) rn,
               (@landing_id:=fish_landing.landing_id)
        FROM (SELECT @landing_id:=0, @row_number:=0) vars,
                  fish_landing 
        LEFT JOIN percentage ON percentage.landing_id = fish_landing.landing_id 
        LEFT JOIN ( SELECT landing_id, MAX(date_added) AS max_date_added 
                    FROM percentage 
                    GROUP BY landing_id) AS dt 
            ON dt.landing_id = percentage.landing_id 
            AND dt.max_date_added = percentage.date_added
        ORDER BY fish_landing.landing_id ASC, percentage.date_added DESC
     ) cte
WHERE cte.rn = 1
ORDER BY cte.landing_id ASC;
Related Question