Getting Distinct records from non-distinct table

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)

Getting Distinct records from non-distinct table

Author

Topic

rohcky
Starting Member

38 Posts

Posted - 2007-08-07 : 09:42:30

I have a table that has what is supposedly a unique customer ID with multiple customers connected to that customer ID.

CustID Name Phone
1 John Doe 555-5555
1 John Doe 555-5556
1 Jane Doe 555-5555
2 Joe Doe 555-5557
3 Jeff Doe 555-5558
3 Jack Doe 555-5559

The results I want are:

CustID Name Phone
1 John Doe 555-5555
2 Joe Doe 555-5557
3 Jeff Doe 555-5558

The names and numbers aren't really a concern to me, I just need to show the unique customer ID.

There was a similar post, http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=87234, but I didn't want to hijack the post.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-07 : 09:48:36

Select custid,name,min(phone) as phone from table
group by custid,name

Madhivanan

Failing to plan is Planning to fail

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-08-07 : 10:14:29

[code]SELECT CustID, [Name], Phone
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY [Name] DESC) AS RowID
,CustID, [Name], Phone
FROM YourTable
) D
WHERE RowID = 1[/code]

rohcky
Starting Member

38 Posts

Posted - 2007-08-07 : 11:26:28

quote:
Originally posted by madhivanan

Select custid,name,min(phone) as phone from table
group by custid,name

Wouldn't that give me the following results?

CustID Name Phone
1 John Doe 555-5555
1 Jane Doe 555-5555
2 Joe Doe 555-5557
3 Jeff Doe 555-5558
3 Jack Doe 555-5559

rohcky
Starting Member

38 Posts

Posted - 2007-08-07 : 14:10:59

Ifor,

I'm getting this error message when trying to utilize you SQL String, "The OVER SQL construct or statement is not supported." Any ideas?

DB on SQL Server 2005.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-08 : 02:41:13

quote:
Originally posted by rohcky

quote:
Originally posted by madhivanan

Select custid,name,min(phone) as phone from table
group by custid,name

Wouldn't that give me the following results?

CustID Name Phone
1 John Doe 555-5555
1 Jane Doe 555-5555
2 Joe Doe 555-5557
3 Jeff Doe 555-5558
3 Jack Doe 555-5559

Yes it is. Try this

Select custid,min(name) as name ,min(phone) as phone from table
group by custid

Madhivanan

Failing to plan is Planning to fail

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-08-08 : 04:40:29

quote:
Originally posted by rohcky

Ifor,

I'm getting this error message when trying to utilize you SQL String, "The OVER SQL construct or statement is not supported." Any ideas?

DB on SQL Server 2005.

Maybe your db is in 2000 compatibility mode.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources