Only select first row of repeating value in a column in SQL

You can use a EXISTS semi-join to identify candidates:

Select wanted rows:

SELECT * FROM tbl tWHERE  NOT EXISTS (    SELECT *    FROM   tbl    WHERE  col1 = t.col1    AND    id = t.id - 1    )ORDER  BY id;

Get rid of unwanted rows:

DELETE FROM tbl AS t-- SELECT * FROM tbl t  -- check first?WHERE EXISTS (    SELECT *    FROM   tbl    WHERE  col1 = t.col1    AND    id   = t.id - 1    );

This effectively deletes every row, where the preceding row has the same value in col1, thereby arriving at your set goal: only the first row of every burst survives.

I left the commented SELECT statement because you should always check what is going to be deleted before you do the deed.

Solution for non-sequential IDs:

If your RDBMS supports CTEs and window functions (like PostgreSQL, Oracle, SQL Server, ... but not SQLite prior to v3.25, MS Access or MySQL prior to v8.0.1), there is an elegant way:

WITH cte AS (    SELECT *, row_number() OVER (ORDER BY id) AS rn    FROM   tbl    )SELECT id, col1FROM   cte cWHERE  NOT EXISTS (    SELECT *    FROM   cte    WHERE  col1 = c.col1    AND    rn   = c.rn - 1    )ORDER  BY id;

Another way doing the job without those niceties (should work for you):

SELECT id, col1FROM   tbl tWHERE  (    SELECT col1 = t.col1    FROM   tbl    WHERE  id < t.id    ORDER  BY id DESC    LIMIT  1) IS NOT TRUEORDER  BY id;

select min(id), Col1 from tableName group by Col1 

If your RDBMS supports Window Aggregate functions and/or LEAD() and LAG() functions you can leverage them to accomplish what you are trying to report. The following SQL will help get you started down the right path:

SELECT id     , Col AS CurCol     , MAX(Col)       OVER(ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS PrevCol     , MIN(COL)       OVER(ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS NextColFROM MyTable

From there you can put that SQL in a derived table with some CASE logic that if the NextCol or PrevCol is the same as CurCol then set CurCol = NULL. Then you can collapse eliminate all the id records CurCol IS NULL.

If you don't have the ability to use window aggregates or LEAD/LAG functions your task is a little more complex.

Hope this helps.