August 22, 2008 at 6:57 pm
I can't publish real data here, for obvious reasons. I don't think multiple names makes the difference. This is what I am getting:
Duck J Donald 999999999
Duck J Donald 999999991
Duck J Donald 999999999
And, this is what I want to get:
Duck J Donald 999999999
Duck J Donald 999999991
I believe I can get there just by eliminating duplicates, but correct me if I'm wrong.
Thanks!
August 23, 2008 at 6:08 am
marius.draganescu (8/22/2008)
I can't publish real data here, for obvious reasons. I don't think multiple names makes the difference. This is what I am getting:Duck J Donald 999999999
Duck J Donald 999999991
Duck J Donald 999999999
And, this is what I want to get:
Duck J Donald 999999999
Duck J Donald 999999991
I believe I can get there just by eliminating duplicates, but correct me if I'm wrong.
Thanks!
you are trying to return distinct rows so this would do the trick for you
SELECT distinct a.[Short_Last_Name], a.[Short_First_Name], a.[Middle_Initial], a.[Tax_ID_Number]
FROM [dbo].[CIS_Acct_Name] a
INNER JOIN
(SELECT Short_Last_Name, Short_First_Name, Middle_Initial, COUNT(DISTINCT Tax_ID_Number) AS CountSSN
FROM [dbo].[CIS_Acct_Name] b GROUP BY Short_Last_Name, Short_First_Name, Middle_Initial
HAVING COUNT(DISTINCT Tax_ID_Number) > 1) AS Dup
ON a.[Short_Last_Name] = Dup.Short_Last_Name AND a.[Short_First_Name] = Dup.Short_First_Name
AND a.[Middle_Initial] = Dup.Middle_Initial WHERE [TAX_ID_Number] <> '0'
ORDER BY Short_Last_Name, Short_First_Name
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 23, 2008 at 9:41 am
I will try this out Monday and I will let you know how it works. Many thanks!!!
August 25, 2008 at 4:35 am
marius.draganescu (8/23/2008)
I will try this out Monday and I will let you know how it works. Many thanks!!!
no problem, table aliasing also helps to compact\tidy your code a little (dont have to keep re typing those long table identifiers 😉 )
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 25, 2008 at 8:16 am
Use This Query to Find Duplicate Rows
Select * from Person
Where Exists(Select 1 from Person A
WHERE Person.FirstName = A.FirstName
ANd Person.LastName = A.LastName
Group BY A.FirstName , A.LastName
Having Count(1)>1)
August 25, 2008 at 10:12 am
Perry,
It looks like this is it!! It is working...
can you recommend a book/ website/ anything else I could use to learn the more complex TSQL from? I do have a programing background but on this occasion I found out that working with a set of rows is quite different...
Thanks again!
August 25, 2008 at 10:37 am
If you are looking for distinct rows (based on the sample data), why the joins, group bys, and having count(*) > 1? I'd think the following would work just as well.
create table #TempTable (
FirstName varchar(25),
MiddleInit char(1),
LastName varchar(25),
TaxID varchar(20)
);
insert into #TempTable
select 'Duck','J','Donald',999999999 union all
select 'Duck','J','Donald',999999991 union all
select 'Duck','J','Donald',999999999;
select
FirstName,
MiddleInit,
LastName,
TaxID
from
#TempTable; -- show all records in the table
select distinct
FirstName,
MiddleInit,
LastName,
TaxID
from
#TempTable; -- show distinct records in the table
drop table #TempTable;
😎
August 25, 2008 at 11:12 am
Lynn,
It's not all the "distinct" records I need to return, it is all records with a duplicate SSN that I am interested in. I tried the query, and it returns everything, for example:
Donald Duck 111111111 (valid)
Donald Duck 222222222 (valid)
Huey Duck 333333333 (distinct record, but I don't want it here,
)
Thanks!
August 25, 2008 at 12:14 pm
marius.draganescu (8/25/2008)
Lynn,It's not all the "distinct" records I need to return, it is all records with a duplicate SSN that I am interested in. I tried the query, and it returns everything, for example:
Donald Duck 111111111 (valid)
Donald Duck 222222222 (valid)
Huey Duck 333333333 (distinct record, but I don't want it here,
)
Thanks!
Okay, that is what I was asking you in this post http://www.sqlservercentral.com/Forums/FindPost557608.aspx but your sample data did not make that clear to me until this last post.
😎
August 25, 2008 at 2:04 pm
marius.draganescu (8/25/2008)
Perry,It looks like this is it!! It is working...
can you recommend a book/ website/ anything else I could use to learn the more complex TSQL from? I do have a programing background but on this occasion I found out that working with a set of rows is quite different...
Thanks again!
your welcome, glad to help. There are literally hundreds of books available McGraw Hill, O Reilly and Wrox tend to be my favourite publishers
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 25, 2008 at 2:11 pm
Lynn Pettis (8/25/2008)
why the joins, group bys, and having count(*) >
the group by would be required for the count function i think
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 25, 2008 at 2:19 pm
Perry Whittle (8/25/2008)
Lynn Pettis (8/25/2008)
why the joins, group bys, and having count(*) >the group by would be required for the count function i think
True, but my knowledge of the problem domain was limited by the information provided. With OP's last post, my understanding was enhanced and the solution provided previously then made sense.
😎
August 26, 2008 at 10:10 am
Lynn Pettis (8/25/2008)
True, but my knowledge of the problem domain was limited by the information provided. With OP's last post, my understanding was enhanced and the solution provided previously then made sense.😎
quite agree Lynn, did seem a bit confusing. We got there in the end :smooooth:
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply