Search code examples
sqlgroup-byprocedure

Collapse SQL rows

Say I have this table:

id  |  name  
-------------
1   |  john  
2   |  steve
3   |  steve
4   |  john
5   |  steve

I only want the rows that are unique compared to the previous row, these:

id  |  name  
-------------
1   |  john  
2   |  steve
4   |  john
5   |  steve

I can partly achieve this by using this query:

SELECT *, (
    SELECT `name` FROM demotable WHERE id=t.id-1
) AS prevName FROM demotable AS t GROUP BY prevName ORDER BY id ASC

But when I am using a query with multiple UNIONs and stuff, this gets way to complicated. Is there an easy way to do this (like GROUP BY, but than more specific)?

Solution

  • This should work, but I don't know if it's simpler :

    select demotable.*
from demotable
left join demotable as prev on prev.id = demotable.id - 1
where demotable.name != prev.name