pulling like data

[osbornebckk]

I have a problem I'm hoping someone can help me find an simple solution to.
I have a field named ACCOUNT_NO. Data in this field is 11 digit numbers. I need to pull a list of accounts from this field.
The list is to be all accounts that have the first 7 digits of the data as duplicates... ie not unique.
example..
ACCOUNT_NO
12345670001
12345671000
12345660001
the query would give me just the 2 accounts 12345670001 and 12345671000.

I'm just beginning with SQL and would really appreciate any help.

Thanks,
Brian

 

[gmmastros]

Select T.Account_No
From   [!]YourTableNameHere[/!] As T
       Inner Join (
         Select LEFT(Account_NO, 7) As Account_NO
         From   [!]YourTableNameHere[/!]
         Group By LEFT(Account_NO, 7)
         Having COUNT(*) > 1
         ) As A
         On LEFT(T.Account_NO, 7) = A.Account_NO

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom