1

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.

1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.