MySQL ORDER BY field with a toggle

Question

I have a mysql result that looks like this:

| compare | field1 | field2 | much more |
-----------------------------------------
|   new   |    x1  |    y   | ...       |
|   old   |    x1  |    y   | ...       |
|   old   |    x2  |    y   | ...       |
|   new   |    x2  |    y   | ...       |

The Query searches for differences of two tables (new and old) and orders it by field1 (with lots more fields, but lets make it simple here). So, the query is returning everything correct but one thing: In the query, there is a UNION ALL with FIRST fetch old, then UNION ALL it to SECOND new.

What i want to have is a result, where my "compare" has ALWAYS FIRST an old and then the new. But see my result, sometimes i get new first and this will break the rest of the code.

I tried it with

ORDER BY FIELD(compare, 'old', 'new'), ...

but it did not work.

Anyone with an idea? Result should be like this:

| compare | field1 | field2 | much more |
-----------------------------------------
|   old   |    x1  |    y   | ...       |
|   new   |    x1  |    y   | ...       |
|   old   |    x2  |    y   | ...       |
|   new   |    x2  |    y   | ...       |

Edit #1:

Here is the complete Query for this:

SELECT
  MIN(compareTables) AS 'table',
  CONCAT(ObjNr, PFKt, WhgNr, DatVon, AdrNr, KEY_Firma) AS 'primary',
  ObjNr,
  PFKt,
  WhgNr,
  DatVon,
  DatBis,
  AdrNr,
  KEY_Firma,
  Info,
  PKto,
  FI_MieterPKto,
  PFktArtEigMiet,
  ST_SchlAustriaPKto,
  inaktiv,
  inaktivBis
FROM ((SELECT
      'old' AS compareTables,
      ObjNr,
      PFKt,
      WhgNr,
      DatVon,
      DatBis,
      AdrNr,
      KEY_Firma,
      Info,
      PKto,
      FI_MieterPKto,
      PFktArtEigMiet,
      ST_SchlAustriaPKto,
      inaktiv,
      inaktivBis
    FROM table_old.PF_PFkt) UNION ALL (SELECT
      'new' AS compareTables,
      ObjNr,
      PFKt,
      WhgNr,
      DatVon,
      DatBis,
      AdrNr,
      KEY_Firma,
      Info,
      PKto,
      FI_MieterPKto,
      PFktArtEigMiet,
      ST_SchlAustriaPKto,
      inaktiv,
      inaktivBis
    FROM table_new.PF_PFkt)) AS compareTables
WHERE 'primary' IS NOT NULL
GROUP BY ObjNr,
         PFKt,
         WhgNr,
         DatVon,
         DatBis,
         AdrNr,
         KEY_Firma,
         Info,
         PKto,
         FI_MieterPKto,
         PFktArtEigMiet,
         ST_SchlAustriaPKto,
         inaktiv,
         inaktivBis
HAVING COUNT(*) = 1
ORDER BY ObjNr, PFKt, WhgNr, DatVon, AdrNr, KEY_Firma

and part of the result

score 3 · Answer 1 · answered Jun 14 '19 at 12:12

3

First order by field1. Then order by compare descending, to get old before new.

ORDER BY field1, compare DESC

answered Jun 14 '19 at 12:12

jarlh

42,561
8
45
63

Doesn't work, sry. field1 will have more than once the same content (for example, field 1 is an planeid with value 10, when having multiple changes to the same plane, it appears more often). – Paladin Jun 14 '19 at 12:18
I tried this out and it breaks the ordering of a new and old belonging together – Paladin Jun 14 '19 at 12:19
2

Seems like you need better sample data, to illustrate the actual problem. – jarlh Jun 14 '19 at 12:19
Just imagine, that in my first post, field1 can have the same data, also field2, but the change is in field_x and i do not know how many fields coming (it's a dynamic sql generator for several problems) – Paladin Jun 14 '19 at 12:21
1

This is definitely correct answer to the question you asked above, if it does not help then you didn't ask the question right. You should give a better sample data – Aleksa Milosevic Jun 14 '19 at 12:49
Made an edit #1 for my question, hope, this will help to get it right ;) – Paladin Jun 14 '19 at 13:04

score 0 · Answer 2 · answered Jun 14 '19 at 11:57

0

You want something like this:

order by field1, field2, (compare = 'OLD') DESC

answered Jun 14 '19 at 11:57

Gordon Linoff

1,242,037
58
646
786

No, sry, this will give me all "old" first and then all "new", but this must toggle with always "old" first ;) – Paladin Jun 14 '19 at 11:59
3

@Paladin . . . No, that is not what this does. The condition on `compare` is the *third* key in the `order by`. – Gordon Linoff Jun 14 '19 at 12:14
I have the SQL here in my Editor and i fired it with your change on third pos of orderby, the result was all "old" first and after that all "new" – Paladin Jun 14 '19 at 12:17

score -1 · Answer 3 · edited Jun 14 '19 at 19:30

-1

Try this:

order by (field1, field2 DESC), compare = 'OLD' DESC

edited Jun 14 '19 at 19:30

Alex Myers

6,196
7
23
39

answered Jun 14 '19 at 12:39

user11647914

1

1

this does not work, i get an error message ```Operand should contain 1 column(s)``` – Paladin Jun 14 '19 at 12:57

MySQL ORDER BY field with a toggle

Edit #1:

3 Answers3