CTE clause not able to be used in final ORDER BY statement

Question

Trying to order by distance using an input geometry t(x), while comparing to geometries from a JOINed table.

WITH "nearest_groups" as (
    SELECT groups.id, ST_Distance(t.x, locations.center) AS nearest
    FROM (SELECT ST_GeographyFromText('SRID=4326;POINT(-121.0611 39.2191)')
    ) AS t(x), groups
    INNER JOIN locations ON groups.location_id = locations.id
    WHERE ST_DWithin(t.x, locations.center, 300000)
) 
SELECT *
FROM "groups"
INNER JOIN "nearest_groups" ON "groups"."id" = "nearest_groups"."id" 
ORDER BY "nearest_groups"."nearest" asc

Error: column "nearest_groups.nearest" must appear in the GROUP BY clause or be used in an aggregate function*

I don't understand what the error measure needs me to do to make this query work. Does it make sense to throw an GROUP BY in there? I'm not familiar with aggregate functions either.

(!!!) The query seems to work fine in PSQL but not in our ORM env (bookshelfjs/Knex). I find this alarming; ORMs give me the ever present fear that I will have to arm-wrestle them into doing what I want

UPDATE: We are using GraphQL and a common pattern is to fetch items that can be paged by tacking on a hasMore boolean and total count. SO Somewhere else, 'hasMore' and 'total' are being compiled, and it is THERE that this error is being throw, since those are using an aggregate function

Answer 1

The syntax of the query is generally correct.

However, GROUPS is a reserved word in standard SQL (at least in SQL:2016 and SQL:2011). But "non-reserved" in Postgres.

This might explain why the query works in psql (talking to a Postgres server!), but not in GraphQL.

Don't use reserved words as identifier. If you are stuck with your choice, you must always double-quote the name, like "groups".

Aside 1

Postgres reduced the number of reserved words in recent releases (and strives too keep the number to a minimum). But standard SQL is still in the habit of reserving (too) many words, and many RDBMS go along with that. For "portable" SQL code (it's never really portable), avoid any and all reserved words, even those accepted by Postgres.

Aside 2

Assuming "groups".id is defined UNIQUE NOT NULL (like the PK), the query can be much simpler and cheaper:

SELECT g.*, st_distance(t.x, l.center) AS nearest
FROM  (SELECT ST_GeographyFromText('SRID=4326;POINT(-121.0611 39.2191)')) AS t(x)
     , "groups"  g
JOIN   locations l ON g.location_id = l.id
WHERE  ST_DWithin(t.x, l.center, 300000)
ORDER  BY nearest;

Only one instance of "groups".id is in the result, which should be welcome side effect.