SQL using N-1 Dense Rank to filter out rows in dense rank N Mysql-CodePudding

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!

CodePudding user response：

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.