If my title isn't clear here's an example of what I am talking about.
Let's say I have a table that looks like this:
+----+------+----+----+---------+-------+---------+-------+
| ID | Time | X | Y | X_START | X_END | Y_START | Y_END |
+----+------+----+----+---------+-------+---------+-------+
| 1 | 0 | 8 | 6 | 6 | 10 | 4 | 8 |
| 2 | 0 | 20 | 10 | 18 | 22 | 8 | 12 |
| 3 | 1 | 8 | 8 | 6 | 10 | 6 | 10 |
| 4 | 1 | 10 | 24 | 8 | 12 | 22 | 26 |
+----+------+----+----+---------+-------+---------+-------+
If I apply this query to the table:
WITH
cte1 AS (SELECT *,
DENSE_RANK() OVER (ORDER BY TIME) AS DENSE_RANK
FROM data
ORDER BY TIME)
SELECT * FROM cte1
I get:
+----+------+----+----+---------+-------+---------+-------+------------+
| ID | Time | X | Y | X_START | X_END | Y_START | Y_END | DENSE_RANK |
+----+------+----+----+---------+-------+---------+-------+------------+
| 1 | 0 | 8 | 6 | 6 | 10 | 4 | 8 | 1 |
| 2 | 0 | 20 | 10 | 18 | 22 | 8 | 12 | 1 |
| 3 | 1 | 8 | 8 | 6 | 10 | 6 | 10 | 2 |
| 4 | 1 | 10 | 24 | 8 | 12 | 22 | 26 | 2 |
+----+------+----+----+---------+-------+---------+-------+------------+
Now what I want to do is filter out any rows where X is within the range X_START - X_END AND Y is within the range Y_START - Y_END AND DENSE_RANK is n-1
so I'd like a result that looks like this:
+----+------+----+----+---------+-------+---------+-------+------------+
| ID | Time | X | Y | X_START | X_END | Y_START | Y_END | DENSE_RANK |
+----+------+----+----+---------+-------+---------+-------+------------+
| 1 | 0 | 8 | 6 | 6 | 10 | 4 | 8 | 1 |
| 2 | 0 | 20 | 10 | 18 | 22 | 8 | 12 | 1 |
| 4 | 1 | 10 | 24 | 8 | 12 | 22 | 26 | 2 |
+----+------+----+----+---------+-------+---------+-------+------------+
I am pretty new to SQL so I am not too sure on how to go about this. Thank you for all help in advance!
Use MIN()
window function to identify the minimum ID
for each Time
so that you can exclude that row if all the other conditions are satisfied too:
WITH cte AS (
SELECT *,
DENSE_RANK() OVER (ORDER BY TIME) AS `DENSE_RANK`,
MIN(ID) OVER (PARTITION BY TIME) min_id
FROM data
)
SELECT ID, Time, X, Y, X_START, X_END, Y_START, Y_END, `DENSE_RANK`
FROM cte
WHERE `DENSE_RANK` = 1
OR NOT (ID = min_id AND X BETWEEN X_START AND X_END AND Y BETWEEN Y_START AND Y_END)
ORDER BY `DENSE_RANK`, ID;
See the demo.