0

I just moved my website to a new server. The new server is using a much newer version of MySQL, and this is causing some problems with GROUP BY and ORDER BY. I am wondering if anybody could help me resolve this, since nothing I've tried seems to help.

My query looks like this:

    SELECT ib_users.`id`
         , ib_users.`name`
         , ib_users.`surname`
         , ib_users.`memberId`
         , payment.`payment_date`
         , payment.`pif`
         , membership.`membership_date`
         , membership.`type`
      FROM `ib_users`
 LEFT JOIN (   SELECT userid
                    , payment_date
                    , pif
                 FROM (  SELECT ib_membershipfees.`userid`
                              , ib_membershipfees.`fordate` `payment_date`
                              , ib_membershipfees.`pif`
                           FROM `ib_membershipfees`
                       ORDER BY payment_date DESC, added DESC
                    ) vt
             GROUP BY userid 
         ) `payment`
        ON payment.`userid` = ib_users.`id`
 LEFT JOIN (  SELECT userid
                   , membership_date
                   , type
                FROM (  SELECT ib_membership.`userid`
                             , ib_membership.`date` `membership_date`
                             , ib_membership.`type`
                          FROM `ib_membership`
                      ORDER BY membership_date DESC, added DESC
                   ) vt
           GROUP BY userid 
         ) `membership`
        ON membership.`userid` = ib_users.`id`
     WHERE ib_users.`removed` = 'no'

The problem is here:

ORDER BY payment_date DESC, added DESC) vt
                GROUP BY userid ) `payment` ON payment.`userid` = ib_users.`id`

The ordering is not working as it did before, causing it to returning the wrong row from ib_membershipfees since ORDER BY payment_date DESC is not being applied for some reason.

Does anybody know why this is not working anymore, and how I can work around it? Any help is very appreciated.

yunzen
  • 32,854
  • 11
  • 73
  • 106
E-g
  • 524
  • 2
  • 12
  • Missing comma? `\`ib_membershipfees.\`fordate\` \`payment_date\`,` – RiggsFolly Mar 25 '20 at 09:48
  • And missing comma?? `ib_membership.\`date\` \`membership_date\`,` – RiggsFolly Mar 25 '20 at 09:49
  • You can remove the `ORDER BY`in the inner queries, the only thing they do is taking time, because you are using the result in a `LEFT JOIN` and after that using `GROUP BY`, so if you remove **ORDER BY payment_date DESC, added DESC** and **ORDER BY membership_date DESC, added DESC** you will get the same result and it will be faster – nacho Mar 25 '20 at 09:54
  • 1
    `ORDER BY` without `LIMIT` in subquery is always ignored (it is lost in outer query). Use ordering in the most outer query only. – Akina Mar 25 '20 at 09:55
  • @nacho you are right about that, but this is only true for newer versions of mysql, which is causing the problem. I am looking for a workaround for this, so that i can group them AND order them as was possible before. If I remove them I get the same result yes, but it is the wrong result. – E-g Mar 25 '20 at 10:04
  • Just get rid of the group by SUB SELECTs and LIMIT the inner output to one line. Instead of `(SELECT ... FROM (SELECT ... ORDER BY ... ) GROUP BY ...)` do `(SELECT ... ORDER BY ... LIMIT 1` – yunzen Mar 25 '20 at 10:12
  • @E-g, it is true for any version. The inner ORDER BY are useless unless you use LIMIT. If you get the wrong result is because your query is wrong – nacho Mar 25 '20 at 10:28

0 Answers0