How to extract unique Company name with ID

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

How to extract unique Company name with ID

Author

Topic

Noel20052006
Starting Member

1 Post

Posted - 2011-08-24 : 10:14:35

Hi All,

I am new comer in this forum and I an trying to learn SQL. I have a learned a little and at the basic level. I am facing one chhallenges whcile working in SQL.

I have table (dbo.Records_Raw) consisting of 5,000,000 data and it is multiple list means company names are there for multiple times.

I want a unique list with all other info. I am using the following query to get the info and i am getting only company names.

select [Company Name]
From dbo.Records_Raw UNION select [Company Name] from dbo.Records_Raw order by [Company Name]

But if i add ID or other column names it is showing the whole list.

How will will I get Unique company names with all other info?

Waiting for your reply..

Thanks in advance.

Niladri Biswas

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-08-24 : 11:06:52

You have to decide which row you want the other columns to come from.

Assuming you are using SQL2005, or above, the following will get the details with the largest id for each company:


WITH CompanyOrder
AS
(
	SELECT *
		,ROW_NUMBER() OVER (PARTITION BY [Company Name] ORDER BY CompanyID DESC) AS RowNum
	FROM dbo.Records_Raw
)
SELECT *
FROM CompanyOrder
WHERE RowNum = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 12:02:20

[code]
SELECT c.*
FROM CompanyOrder c
INNER JOIN (SELECT [Company Name],MAX(CompanyID) AS MaxComp
FROM CompanyOrder
GROUP BY [Company Name])c1
ON c1.[Company Name] = c.[Company Name]
AND c1.MaxComp = c.CompanyID
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources