CODEWITHSUNDEEP
sql
FastTrack
FastTrack

Reputation: 8980

Return unique rows based on one column

I'm using SQL Server 2008...

I have this result set:

ID        Name
------------------
717892    John
717892    Bill
717892    Steve
717200    Mark
717340    Gerald
717340    Frank

Here is what I want:

ID        Name
------------------
717892    John
717200    Mark
717340    Frank

I don't care which specific rows are returned, I just need to see the unique rows based on the ID column.

I tried this:

SELECT DISTINCT ID, * FROM #tempTable;

But I get the following error:

The text data type cannot be selected as DISTINCT because it is not comparable.

How do I accomplish this?

Upvotes: 0

Views: 78

Answers (1)

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

;WITH cte AS
(
SELECT ID,Name,
       row_number() over (partition by id order by name) as row
From tableName
)

Select ID,Name
FROM cte
Where row=1

Upvotes: 1

Related Questions