CartoDB - PostgreSQL row_number() using PostGIS ST_Distance

Question

I'm currently working on a query that should return a subset (i.e. "new") CartoDB table sorted by proximity to a given point. I want to display labels on the map corresponding to the closest, second closest, etc. and thought to do that by using the Postgres row_number() method in a new column:

SELECT
    *,
    ST_Distance(
        ST_GeomFromText('Point(-73.95623080000001 40.6738101)', 4326)::geography,
        the_geom::geography
    ) / 1609 AS dist,
    row_number() OVER (ORDER BY dist) as rownum
FROM locations
WHERE ST_Intersects(
   ST_GeomFromText(
      'Point(-73.95623080000001 40.6738101)', 4326
   ),
   the_geom
)
ORDER BY dist ASC

However, when I try this, CartoDB returns the following error:

Error: column "dist" does not exist

Any suggestions on a better approach or something I'm missing?

Answer 1

SELECT
  *,
  row_number() OVER (ORDER BY dist) as rownum
FROM (
  SELECT
      *,
      ST_Distance(
          ST_GeomFromText('Point(-73.95623080000001 40.6738101)', 4326)::geography,
          the_geom::geography
      ) / 1609 AS dist
  FROM locations
  WHERE ST_Intersects(
     ST_GeomFromText(
        'Point(-73.95623080000001 40.6738101)', 4326
     ),
     the_geom
  )
) a
ORDER BY dist ASC

You need to use a nested query. See the docs on why aliases cannot be used in ORDER BY statements.