How to get the first record of each type in sequence?-CodePudding

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?

CodePudding user response：

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.