Distinct value - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Distinct value

Author

Topic

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-04-01 : 18:44:02

I have company table:

Compid department Address city zip

How I do i get distinct compid ,department with same address,city and zip? Also for different address,city and zip

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-04-01 : 19:48:38

Well just use the distinct keyword in your search. as long as CompID is unique then you are fine.

[CODE]
SELECT DISTINCT Compid, department, Address, city, zip
FROM COMPANY
[/CODE]

That will return all unique records from the company table. It will not return duplicates. If CompID is a unique key then this will work.

r&r

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-04-01 : 21:59:03

sample data:

Compid department address city zip
1 A A D 1
1 NULL A D 1
2 B C M 2
2 NULL C M 2
3 C M N 3

Output:
1 A A D 1
2 B C M 2

(So I want all where address,city,zip are only duplicate.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-04-01 : 23:42:47

Hi,

Try this Once,

declare @temp table (Compid int,department varchar(32),address varchar(32), city varchar(32),zip int)
insert into @temp select 1, 'A', 'A', 'D', 1
insert into @temp select 1, NULL, 'A', 'D', 1
insert into @temp select 2, 'B', 'C', 'M' ,2
insert into @temp select 2, NULL, 'C', 'M', 2
insert into @temp select 3, 'C', 'M', 'N', 3

select compid,max(department) as department,max(address) as address,max(city) as city,zip
from @temp group by compid,zip

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-02 : 04:14:08

In the above example, use

select t1.* from @temp as t1 inner join
(
select address,city,zip from @temp group by address,city,zip
having count(*)>1
) as t2 on t1.address=t2.address and t1.city=t2.city and t1.zip=t2.zip
where t1.department is not null

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources