-1

Amazon Redshift 1.0.22169

I'm trying to order by company names and place any "N/A" values first. I have a simple test table like so:

+----+-----------+
| id | name      |
+----+-----------+
|  1 | Company 3 |
|  2 | Company 1 |
|  3 | N/A       |
|  4 | Company 2 |
+----+-----------+

With MySQL I use the following:

SELECT 
    `name` AS 'company'
FROM
    `companies`
GROUP BY
    `company`
ORDER BY
    CASE `company` WHEN 'N/A' THEN 1 ELSE 2 END ASC,
    `company` ASC

to get the desired result:

+-----------+
| company   |
+-----------+
| N/A       |
| Company 1 |
| Company 2 |
| Company 3 |
+-----------+

But when trying to do so in Redshift:

SELECT
    "name" AS "company"
FROM
    "companies"
GROUP BY
    "company"
ORDER BY
    CASE "company" WHEN 'N/A' THEN 1 ELSE 2 END ASC,
    "company" ASC

I'm getting the following error:

Query 1 ERROR: ERROR:  column "company" does not exist in companies

Any suggestions how this can be adjusted to work for PostgreSQL?

GMB
  • 216,147
  • 25
  • 84
  • 135
Rick
  • 712
  • 5
  • 23
  • Which ever line the error's on, does it work if you use `"name"` instead of the `"company"` alias there? – Noah Dec 27 '20 at 05:13
  • Yes but I must get it working with the alias, if possible. This is a simplified version of the issue. The real query uses something similar to `COALESCE("companies"."name", "other_table"."name", "another_table"."name", 'N/A') AS "company"`. – Rick Dec 27 '20 at 05:18
  • 1
    @Noah Seems your suggestion is the only way to get it working. Thanks – Rick Dec 27 '20 at 10:13

1 Answers1

0

All databases allow column aliases to be used as keys in the ORDER BY. Some do not allow expressions on aliases. And (surprising to me), Postgres is one of those databases. Redshift is built on the code base of an old version of Postgres, so it probably follows the same rules.

So use the base column name:

ORDER BY (CASE name WHEN 'N/A' THEN 1 ELSE 2 END) ASC,
         name ASC   -- this could also be "company"

Note: If you never have NULL values in the column, you can express this as:

ORDER BY NULLIF(name, 'N/A') NULLS FIRST
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I suspect this is the approach I'll end up having to use because the base column names do work, it just makes it much more difficult in my situation. With such a complicated group that uses conditional checks and various tables to create it the `ORDER BY` requires all those same conditions which gets messy. Thanks for pointing out the `NULLS FIRST` option though! Interesting.. – Rick Dec 27 '20 at 19:17