Untitled

mail@pastecode.io avatar
unknown
plain_text
7 months ago
1.2 kB
3
Indexable
Never
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
Leave a Comment