Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Distinct Of only one Colum

Author  Topic 

chaaru_akilan
Starting Member

16 Posts

Posted - 2013-02-13 : 08:28:06
Hi,
How to select distinct of only one column? and add unique numbering to the result set?

I have a table as:

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

My O/p:
ID Name No
1 ABC Null
2 CDE 78
3 GHF 80

Thanks in advance

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2013-02-13 : 08:35:04
You can use somethink like this:

select t.name, row_number() over ( order by t.name )
from ( select distinct name
from table
) t
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-13 : 08:35:15
[code]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;[/code]
Go to Top of Page

chaaru_akilan
Starting Member

16 Posts

Posted - 2013-02-15 : 03:57:26
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-15 : 04:05:04
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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

chaaru_akilan
Starting Member

16 Posts

Posted - 2013-02-15 : 04:10:27
Great, It worked as i needed!Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-15 : 04:13:38
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -