Distinct results - 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 2000 Forums

SQL Server Development (2000)

Distinct results

Author

Topic

lancepr
Starting Member

17 Posts

Posted - 2005-09-29 : 17:52:52

I have an example table


Account    Name    Address              email
12345      Jim     123 Any street       jim@aol.com
12345      Jim     456 Any Street       jim@aol.com
56487      Bob     777 Any Street       bob@aol.com

I want to get all fields with distinct account #'s (2 rows, 4 fields)
If I do a distinct I would get three rows. I only want jim once because he as the same account#
I really do not care which jim I get as long I get only 1 jim in my query.

My table is pretty big, about 5000 records and about 200 of them have the same account number with multiple addresses.

This is driving me nuts!

Lance

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-29 : 18:18:33

Try this:


select distinct
	a.Account,
	a.Name,
	a.Address,
	a.Email
from
	MyTable a
	join
	(
	select
		bb.Account,
		bb.Name,
		rest= min(bb.Address+bb.Email)
	from
		MyTable bb
	group by
		bb.Account,
		bb.Name
	) b
	on	a.Account = b.Account and
		a.Name = b.Name and
		a.Address+a.Email = b.rest

CODO ERGO SUM

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-29 : 19:02:20

>> My table is pretty big, about 5000 records and about 200 of them have the same account number with multiple addresses.
Big ?
ok, everything is relative

You should put a PRIMARY KEY on the Account column.
It is supposed to be unique, isn't it ?

Note:
EVERY TABLE MUST HAVE AT LEAST ONE UNIQUE CONSTRAINT THAT HAS SOME BUSINESS MEANING
A good & reasonable primary key will get you far.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-30 : 00:58:28

Try this also


Select Distinct Account,Name,
	(Select top 1 Address from yourTable 
	 where Account=T.Account and Name=T.Name and email=T.email),
email from yourTable T

Madhivanan

Failing to plan is Planning to fail

Hippi
Yak Posting Veteran

63 Posts

Posted - 2005-09-30 : 13:38:10

How about
select * from Table1 T
where Address =(select top 1 Address from Table1 T1 where T.account=T1.account and T.name=T1.name)

Hippi

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-01 : 02:02:50

quote:
Originally posted by Hippi

How about
select * from Table1 T
where Address =(select top 1 Address from Table1 T1 where T.account=T1.account and T.name=T1.name)

Hippi

Well. Yours is better than mine

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources