SQL server - advanced grouping

Question

I have at table containing procurement contracts that looks like this:

+------+-----------+------------+---------+------------+-----------+
| type | text      | date       | company | supplierID | name      |
+ -----+-----------+------------+---------+------------+-----------+
| 0    | None      | 2004-03-29 | 310     | 227234     | HRC INFRA |
| 0    | None      | 2007-09-30 | 310     | 227234     | HRC INFRA |
| 0    | None      | 2010-11-29 | 310     | 227234     | HRC INFRA |
| 2    | Strategic | 2011-01-01 | 310     | 227234     | HRC INFRA |
| 0    | None      | 2012-04-10 | 310     | 227234     | HRC INFRA |
+------+-----------+------------+---------+------------+-----------+

In this example the first three rows the contract is the same. So I only want the first one. The row with type = 2 is a change in procurement contract with the given supplier. I want to select that row as well. On the last row the contract changes back to 0, so I want to select that row as well.

Basically I want to select the first row and the rows where the contract type changes. So the result should look like this:

+------+-----------+------------+---------+------------+-----------+
| type | text      | date       | company | supplierID | name      |
+ -----+-----------+------------+---------+------------+-----------+
| 0    | None      | 2004-03-29 | 310     | 227234     | HRC INFRA |
| 2    | Strategic | 2011-01-01 | 310     | 227234     | HRC INFRA |
| 0    | None      | 2012-04-10 | 310     | 227234     | HRC INFRA |
+------+-----------+------------+---------+------------+-----------+

Any suggestions to how I can accomplish this?

Answer 1

;WITH cte AS
 (
  SELECT ROW_NUMBER() OVER (ORDER BY date) AS Id,
         type, text, date, company, supplierId, name
  FROM your_table
  )
  SELECT c1.type, c1.text, c1.date, c1.company,
         c1.supplierId, c1.name
  FROM cte c1 LEFT JOIN cte c2 ON c1.id = c2.id + 1
  WHERE c2.text IS NULL OR c1.text != c2.text

Demo on SQLFiddle