Table Data:
| ID | Type |
|---|---|
| 1 | A |
| 2 | A |
| 3 | B |
| 4 | A |
| 5 | A |
| 6 | B |
| 7 | B |
| 8 | A |
| 9 | A |
| 10 | A |
How to get only rows with IDs 1,3,4,6,8, or the first records on type-change by single query?
We were doing this in code using multiple queries and extensive processing especially for large data, is there a way to do this in a single query?
>Solution :
Use LAG() window function to get for every row the previous row’s type and compare it to the current type.
Create a flag column that is true if the 2 types are different and use it to filter the table:
WITH cte AS (
SELECT *, type <> LAG(type, 1, '') OVER (ORDER BY id) flag
FROM tablename
)
SELECT * FROM cte WHERE flag;
I assume that the column type does not contain empty values (nulls or
empty strings).
See the demo.