SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Distinct Of only one Colum
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chaaru_akilan
Starting Member

India
16 Posts

Posted - 02/13/2013 :  08:28:06  Show Profile  Reply with Quote
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
Starting Member

45 Posts

Posted - 02/13/2013 :  08:35:04  Show Profile  Reply with Quote
You can use somethink like this:

select t.name, row_number() over ( order by t.name )
from ( select distinct name
from table
) t

Edited by - Luuk123 on 02/13/2013 08:35:35
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 02/13/2013 :  08:35:15  Show Profile  Reply with Quote
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;
Go to Top of Page

chaaru_akilan
Starting Member

India
16 Posts

Posted - 02/15/2013 :  03:57:26  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 02/15/2013 :  04:05:04  Show Profile  Reply with Quote
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

India
16 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/15/2013 :  04:13:38  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000