Untitled
unknown
plain_text
2 years ago
1.2 kB
7
Indexable
SELECT match.street_name, match.city, match.current_id, match.past_id FROM (SELECT ROW_NUMBER() OVER ( PARTITION BY p.id ORDER BY c.street_name, c.city ) current_row_num, ROW_NUMBER() OVER ( PARTITION BY c.id ORDER BY c.street_name, c.city ) past_row_num, c.street_name AS street_name, c.city AS city, c.id AS current_id, p.id AS past_id, ABS(c.floor_area - p.floor_area) AS floor_area_diff FROM (SELECT street_name AS street_name, city AS city, id AS id, floor_area AS floor_area FROM properties WHERE [current] = 'TRUE' AND quote_id = '645f121b-7fdb-42d3-9c30-1ef678c831b1' ORDER BY street_name ASC, city ASC OFFSET 0 ROWS ) AS c INNER JOIN (SELECT street_name AS street_name, city AS city, id AS id, floor_area AS floor_area FROM properties WHERE [current] = 'FALSE' AND quote_id = '645f121b-7fdb-42d3-9c30-1ef678c831b1' ORDER BY street_name ASC, city ASC OFFSET 0 ROWS ) AS p ON c.street_name = p.street_name AND c.city = p.city ORDER BY street_name ASC, city ASC, floor_area_diff ASC OFFSET 0 ROWS ) AS match WHERE match.current_row_num = 1 AND match.past_row_num = 1
Editor is loading...
Leave a Comment