SQL ORDER BY with CASE with UNION ALL

Question

Running PostgreSQL (7.4 and 8.x) and I thought this was working but now I'm getting errors.

I can run the queries separately and it works just fine, but if I UNION or UNION ALL it throws an error.

This errors out: (Warning: pg_query(): Query failed: ERROR: column "Field1" does not exist ... ORDER BY CASE "Field1" W...)

SELECT "Field1" AS field_1, "Field2" AS field_2,
"Field3" AS field_3, "Field4" AS field_4
FROM "TableName" 
WHERE condition
AND other_condition
UNION ALL
SELECT "Field1" AS field_1, "Field2" AS field_2,
"Field3" AS field_3, "Field4" AS field_4
FROM "TableName" 
WHERE yet_another_condition
AND yet_another_other_condition
ORDER BY CASE "Field1"
    WHEN 'A' THEN 1
    WHEN 'B' THEN 2
    WHEN 'C' THEN 3
    ELSE 4
END

This works:

SELECT "Field1" AS field_1, "Field2" AS field_2,
"Field3" AS field_3, "Field4" AS field_4
FROM "TableName" 
WHERE yet_another_condition
AND yet_another_other_condition
ORDER BY CASE "Field1"
    WHEN 'A' THEN 1
    WHEN 'B' THEN 2
    WHEN 'C' THEN 3
    ELSE 4
END

And this works as well:

SELECT "Field1" AS field_1, "Field2" AS field_2,
"Field3" AS field_3, "Field4" AS field_4
FROM "TableName" 
WHERE condition
AND other_condition
ORDER BY CASE "Field1"
    WHEN 'A' THEN 1
    WHEN 'B' THEN 2
    WHEN 'C' THEN 3
    ELSE 4
END

and if I leave off the ORDER BY and just use the UNION or UNION ALL it works as well.

Any Ideas?

score 15 · Accepted Answer · answered Jun 21 '11 at 14:58

Put everything in another SELECT:

SELECT * FROM (
  SELECT "Field1" AS field_1, "Field2" AS field_2,
  "Field3" AS field_3, "Field4" AS field_4
  FROM "TableName" 
  WHERE condition
  AND other_condition
  UNION ALL
  SELECT "Field1" AS field_1, "Field2" AS field_2,
  "Field3" AS field_3, "Field4" AS field_4
  FROM "TableName" 
  WHERE yet_another_condition
  AND yet_another_other_condition
) As A
ORDER BY CASE field_1
    WHEN 'A' THEN 1
    WHEN 'B' THEN 2
    WHEN 'C' THEN 3
    ELSE 4
END

or, better, use the alias in ORDER BY, as it is passed at the end of the UNION:

  SELECT "Field1" AS field_1, "Field2" AS field_2,
  "Field3" AS field_3, "Field4" AS field_4
  FROM "TableName" 
  WHERE condition
  AND other_condition
  UNION ALL
  SELECT "Field1" AS field_1, "Field2" AS field_2,
  "Field3" AS field_3, "Field4" AS field_4
  FROM "TableName" 
  WHERE yet_another_condition
  AND yet_another_other_condition
  ORDER BY CASE field_1
    WHEN 'A' THEN 1
    WHEN 'B' THEN 2
    WHEN 'C' THEN 3
    ELSE 4
  END

@Phill: In the first place, I didn't noticed you used the field name and not the alias...:) — CristiC, Jun 21 '11 at 15:06

score 1 · Answer 2 · answered Jun 21 '11 at 15:00

1

The first one does not work because you should do

ORDER BY CASE field_1

The "Field1" is only available in a single subquery, and after you make the UNION with a common alias, you cannot refer to that column as "Field1" any more.

answered Jun 21 '11 at 15:00

Benoit

76,634
23
210
236

+1 for helping with the answer. I did this but I get this error: ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns – Phill Pafford Jun 21 '11 at 15:03

SQL ORDER BY with CASE with UNION ALL

2 Answers2

Linked