Xjyuk

been trying to get this query to work, it's almost perfect , but I just need to add the commented out 'where' condition and it'll do exactly what I want, but when I uncomment,I get an error 'column doesnt exist'. Been having trouble figuring out the syntax for this, would appreciate any help!

I basically have a table with date, timestamp, DID, coordinates

I want a query that will return rows with the last coordinate from day X, and the first coord from day X+1 , and the coordinates (so it will only return results where there are 2 consecutive dates)

This is what I came up with

SELECT a.timestamp_intersecting_date d1,b.timestamp_intersecting_date d2, 
 a."DID", 
 a.timestamp_intersecting_max t1, b.timestamp_intersecting_min t2,
 RANK () OVER ( 
 PARTITION BY a.timestamp_intersecting_date
 ORDER BY a.timestamp_intersecting_max DESC
 ) timestamp_d1_rank ,
 RANK () OVER ( 
 PARTITION BY b.timestamp_intersecting_date
 ORDER BY b.timestamp_intersecting_max ASC
 ) timestamp_d2_rank,
 a.coords_centroid, b.coords_centroid
FROM
 signals a
INNER JOIN signals b ON (a."DID" = b."DID")
WHERE (b.timestamp_intersecting_date = a.timestamp_intersecting_date + INTERVAL '1 DAY')
AND a."DID" = b."DID"
--AND timestamp_d1_rank = 1
--AND timestamp_d2_rank = 1
ORDER BY a."DID", t1 desc, t2 asc

thanks!

You cannot use window/ranking functions or their aliases in the WHERE clause because they are evaluated in SELECT, after the WHERE has been evaluated.

You can use a subquery (derived table or CTE) to process a second WHERE clause, after the window functions have been evaluated:

SELECT d1, d2,
 "DID",
 t1, t2,
 coords_centroid_a,
 coords_centroid_b
FROM
 (
 SELECT a.timestamp_intersecting_date d1, b.timestamp_intersecting_date d2, 
 a."DID", 
 a.timestamp_intersecting_max t1, b.timestamp_intersecting_min t2,
 RANK () OVER ( 
 PARTITION BY a.timestamp_intersecting_date
 ORDER BY a.timestamp_intersecting_max DESC
 ) timestamp_d1_rank ,
 RANK () OVER ( 
 PARTITION BY b.timestamp_intersecting_date
 ORDER BY b.timestamp_intersecting_max ASC
 ) timestamp_d2_rank,
 a.coords_centroid AS coords_centroid_a, 
 b.coords_centroid AS coords_centroid_b
 FROM
 signals a
 INNER JOIN signals b ON (a."DID" = b."DID")
 WHERE (b.timestamp_intersecting_date = a.timestamp_intersecting_date + INTERVAL '1 DAY')
 AND a."DID" = b."DID"
 ) AS t
WHERE t.timestamp_d1_rank = 1
 AND t.timestamp_d2_rank = 1
ORDER BY "DID", t1 DESC, t2 ASC ;

You cannot use aliases in WHERE clauses!

To demonstrate, consider this example (fiddle).

CREATE TABLE foo (bar INT);

Populate it:

INSERT INTO foo VALUES (1), (2), (3);

Then

SELECT bar AS baz
FROM foo
WHERE baz = 2;

Causes the error:

ERROR: column "baz" does not exist
LINE 2: where baz = 2;
 ^
HINT: Perhaps you meant to reference the column "foo.bar".

You didn't post your error message or PostgreSQL version, but your problem is that timestamp_d1_rank and timestamp_d2_rank are aliases.

Maybe the PostgreSQL hint isn't obvious in your case because of the Window function? I'm afraid you're going to have to put the entire expression into your WHERE clause! Or, you could consider using a CTE (i.e. WITH clause) as outlined here.

For a good explanation of this issue, see here.