combine consecutive rows if they are matches-CodePudding

I am looking to combine rows into a single row if the data is consecutive. I've looked over gaps and islands and I know I used to do this very regularly with SQL Server. The solution escapes me, but I recall doing some type of ROW_NUMBER() over (Partition BY groupId, name, email, phone ORDER BY id) - ROW_NUMBER() over (ORDER BY id) seq with a calculation. I was not successful in getting this to work.

Here is a sample data set:

Desired Result:

Any help would be greatly appreciated.

CodePudding user response：

If you only want the last row in each group, you can use lead():

select t.*
from (select t.*,
             lead(id) over (order by id) as next_id,
             lead(id) over (partition by groupid, name, email, phone order by id) as next_id_grp
      from t
     ) t
where next_id_grp is null or next_id_grp <> next_id;

This is looking at the next id and the next id for members in the same group. When these are different, then the row is the last row in the group.

This is better than the row_number() approach because that would typically require an aggregation.