Let’s assume you have such a table:

CREATE TABLE benohead(SP1 int, SP2 int, SP3 int)

Column SP1 has non unique values and you want to keep only one row per unique SP1 value.

Assuming we have inserted the following values in the table:

INSERT INTO benohead VALUES(1,2,3)
INSERT INTO benohead VALUES(1,4,5)
INSERT INTO benohead VALUES(1,6,7)
INSERT INTO benohead VALUES(2,3,2)
INSERT INTO benohead VALUES(3,4,6)
INSERT INTO benohead VALUES(3,7,8)
INSERT INTO benohead VALUES(4,1,7)

It’d look like this:

SP1         SP2         SP3
----------- ----------- -----------
          1           2           3
          1           4           5
          1           6           7
          2           3           2
          3           4           6
          3           7           8
          4           1           7

Since SP2 and SP3 can have any value and you could also have rows where all 3 fields have the same value, it’s not so trivial to get a list looking like this:

SP1         SP2         SP3
----------- ----------- -----------
          1           6           7
          2           3           2
          3           7           8
          4           1           7

Even if the table is sorted, iterating through the rows and keeping track of the last SP1 you’ve seen will not help you since you cannot delete the second row because you do not have anything to identify it (like ROW_COUNT in Oracle).

One way to handle it is getting a list of unique SP1 values and their row count:

SELECT SP1, count(*) as rcount FROM benohead GROUP BY SP1

This will return something like this:

SP1         rcount
----------- -----------
          1           3
          2           1
          3           2
          4           1

You can then iterate through this and for each value of SP1 set a rowcount to rcount-1 and delete entries with that SP1 value. In the end, you’ll have one row per SP1 values. Of course, if you just need the data and do not want to actually clean up the table, you’ll have to do it on a copy of the table.

Instead of deleting, you can also iterate through the values of SP1 and fetch the top 1 row for this value:

SELECT TOP 1 SP1, SP2, SP3 FROM benohead WHERE SP1=1

If you had only one additional column (e.g. SP2), it’d be even easier, since you could just use MAX and GROUP BY:

SELECT SP1, MAX(SP2) AS SP2 FROM benohead GROUP BY SP1

which returns:

SP1         SP2
----------- -----------
          1           6
          2           3
          3           7
          4           1

Unfortunately this doesn’t scale to multiple columns. If you also have SP3, you cannot use MAX twice since you will then combinations which didn’t exist in the original table. Let’s insert an additional row:

INSERT INTO benohead VALUES(1,1,9)

The following statement:

SELECT SP1, MAX(SP2) AS SP2, MAX(SP3) AS SP3 FROM benohead GROUP BY SP1

will return:

 SP1         SP2         SP3
 ----------- ----------- -----------
           1           6           9
           2           3           2
           3           7           8
           4           1           7

Although we had no row with SP1=1, SP2=6 and SP3=9.

So if you don’t like the solution iterating and delete with rowcount, you’ll need to introduce a way to uniquely identify each row: an identity column.

You can add an identity column to the table:

ALTER TABLE benohead ADD ID int identity

And them select the required rows like this:

SELECT * from benohead b WHERE b.ID = (SELECT MAX(ID) FROM benohead b2 WHERE b.SP1=b2.SP1)

This will fetch for each value of SP1 the row with the highest ID.

Or you can create a temporary table with an indentity column:

SELECT ID=identity(1), SP1, SP2, SP3 INTO #benohead FROM benohead	

And then use a similar statement on the temporary table.