Multiple Column Distinct query - DONE

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

Transact-SQL (2000)

Multiple Column Distinct query - DONE

Author

Topic

mfriedman81
Starting Member

2 Posts

Posted - 2004-11-17 : 10:22:56

I have a dataset that has invoice data, but the invoice numbers are not unique. I think that a combination of the inovice number and several other variables might create a unique id. In order to test this hypothesis I tried

select count(disntict fieldA, fieldB, etc) from table

But that did not work. I then tried

Select Field A, FieldB, Count(1)
from table
where count(1)>1
Group by Field A, Field B

And that also did not work. Any ideas?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-17 : 10:52:21

try

Select FieldA, FieldB, count(cast(FieldA as varchar(100)) + cast(FieldB as varchar(100)))
from table
Group by Field A, Field B
having count(cast(FieldA as varchar(100)) + cast(FieldB as varchar(100))) > 1

Go with the flow & have fun! Else fight the flow

mfriedman81
Starting Member

2 Posts

Posted - 2004-11-17 : 11:10:02

Thanks, I just should have used having instead of where and put it after the group by. I think that will work. Thanks for your help as I am pretty new to SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources