SELECT ROW_NUMBER() OVER (ORDER BY reqid) AS ID, [name],no
FROM
(
	SELECT *, ROW_NUMBER() OVER (PARTITION BY [name] ORDER BY (SELECT 1)) AS RN
	FROM TheTable
) s WHERE RN = 1;

quote:

---

Originally posted by chaaru_akilan

Hi,
Thank you. I have another additional requirement! I need to concatenate the IDs in one column.Am unable to concatenate.

I have a table as:

REQID Name No
1 ABC Null
2 ABC Null
3 CDE 78
4 GHF 80

My O/p should be
ReqID Name No
1,2 ABC Null
3 CDE 78
4 GHF 80

Thanks In advance

---

SELECT STUFF((SELECT ',' + CAST(REQID AS varchar(10)) 
FROM Table
WHERE Name= t.Name
AND No = t.No
ORDER BY REQID
FOR XML PATH('')),1,1,'') AS REQIDs,
[Name],
NULLIF(No,'Null') AS [No]
FROM (SELECT DISTINCT [Name],COALESCE(No,'Null') AS [No]
FROM table)t