0

I am having a MySQL query like this:

SELECT * FROM (
    SELECT ranking_id, target_page_plain, ranking_date
      FROM keywords_rankings
     WHERE keyword_id = xxx
       AND target_page_plain IS NOT NULL
     ORDER BY ranking_date DESC, ranking_position ASC
) a
GROUP BY target_page_plain
ORDER BY ranking_date DESC

Since some MySQL version change, this does not work anymore like expected. This part here:

SELECT ranking_id, target_page_plain, ranking_date
                  FROM keywords_rankings
                 WHERE keyword_id = xxx
                   AND target_page_plain IS NOT NULL
                 ORDER BY ranking_date DESC, ranking_position ASC

will return the results correctly:

enter image description here

Now with the ORDER and GROUP BY, i want to get 2 rows, with the latest ranking_date.

What i expect would be:

  1. URL A / 2023-06-12
  2. URL B / 2023-02-06

But what i get is this:

  1. URL A / 2022-07-04
  2. URL B / 2022-08-15

Its not returning the grouped by URL with the order ORDER BY ranking_position DESC.

Its really strange, maybe someone can help.

Thanks

Barmar
  • 741,623
  • 53
  • 500
  • 612
ForeverGolf
  • 57
  • 1
  • 8
  • Have you tried doing the `ORDER BY` just once, and with all the fields you want to order by, only on the outer part? I don't think there's any reason to apply an order until it has the entire set of results that needs to be ordered. Anything else would just be luck that it happened to retain the order you hoped for. I am not an SQL expert. – Andrew Morton Jun 16 '23 at 17:21
  • The result of this has never been specified. If you were getting the desired result before the upgrade it was purely by accident, you shouldn't expect it to continue. – Barmar Jun 16 '23 at 17:25
  • When i change it to ```SELECT * FROM ( SELECT ranking_id, target_page_plain, ranking_date,ranking_position FROM keywords_rankings WHERE keyword_id = xxx AND target_page_plain IS NOT NULL ) a GROUP BY target_page_plain ORDER BY ranking_date DESC, ranking_position ASC``` it also not working – ForeverGolf Jun 16 '23 at 18:19

0 Answers0