0

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!

roy naufal
  • 379
  • 1
  • 8
  • 19
  • You can't use column aliases in a `WHERE` clause. See the dupes for workarounds. – Nick Sep 08 '19 at 03:06
  • If this is MySQL, you can use the alias in a `HAVING` clause instead. – Nick Sep 08 '19 at 03:07
  • There is another issue other than using an alias in the where clause (true you cannot), but let's assume that it was valid. This query would still fail. It has an "order of operations" problem. Windowed functions ( RANK, etc) are not evaluated until well after the Where clause is complete. So at the point of these there would be no results as yet from the rank function. To resolve place the current query the (down through AND a."DID" = b."DID") as an inner query. Then wrap an outer query containing the commented predicated and the order by clause as ORDER BY "DID", t1 desc, t2 asc" – Belayer Sep 08 '19 at 20:27

0 Answers0