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

user_3408531 0
Viewed 46

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?

window-functions mysql sql
1 Answers

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.

user_104988280
Related