There are several queries that I try to combine:

SELECT 'object1', MessageNo, _Order FROM _Object1Changes WHERE MessageNo=0 AND Node='33' order by _Order desc LIMIT 1 UNION ALL SELECT 'object2', MessageNo, _Order FROM _Object2Changes WHERE MessageNo=0 AND Node='33' order by _Order desc LIMIT 1 UNION ALL SELECT 'object3', MessageNo, _Order FROM _Object3Changes WHERE MessageNo=0 AND Node='33' order by _Order desc LIMIT 1 UNION ALL SELECT 'object4', MessageNo, _Order FROM _Object4Changes WHERE MessageNo=0 AND Node='33' order by _Order desc LIMIT 1;

Error Code: 1221. Incorrect usage of UNION and ORDER BY

Or try this:

  SELECT 'object1', MessageNo, MIN(_Order) FROM _Object1Changes WHERE MessageNo=0 AND Node='33' LIMIT 1 UNION ALL SELECT 'object2', MessageNo, MIN(_Order) FROM _Object2Changes WHERE MessageNo=0 AND Node='33' LIMIT 1 UNION ALL SELECT 'object3', MessageNo, MIN(_Order) FROM _Object3Changes WHERE MessageNo=0 AND Node='33'LIMIT 1 UNION ALL SELECT 'object4', MessageNo, MIN(_Order) FROM _Object4Changes WHERE MessageNo=0 AND Node='33' LIMIT 1;

I get a table with one entry, 'object1', null, null. The goal is to get the result from several tables, where from each table takes a record with the minimum value _Order and Node = '33 '

How can this be fixed?

  • 3
    apply brackets (select ...order by limit) union all (select ...) - Mike
  • one
    To apply ORDER BY or LIMIT to an individual SELECT, it is necessary to place that clause inside. UNION Syntax . - Akina
  • Yes, this is what you need. What is the difference between MIN (_Order) and order by _Order in this case? - Leonid Dubravsky
  • What is the difference between MIN (_Order) and order by _Order in this case? One warm, the other soft. Where MIN () - you can remove LIMIT, it does not make sense. And the variant with MIN () is guaranteed not slower than the variant with ORDER BY (if the optimizer does not make a mistake, which is rare). - Akina
  • but with MIN, I get a table with empty records such as 'object1', null, null from tables where there is no necessary data and how many records will it yield if there are several records with the minimum _Order value? - Leonid Dubravsky

0