Count

[sleezy]

I WANT TO SELECT IDs THAT HAVE THE FIRST 10 CHARACTERS THAT ARE EQUAL BUT WITH A DIFFERENT CLIENTNO.
hOW DO i ACHIEVE THAT THIS IS MY QUERY

--SELECT ALL IDS INTO TEST TABLE
SELECT CLIENTNO,ID INTO #TEST FROM ADATA

--SELECT THE IDS THAT HAVE A DIFFERENT CLIENT NUMBER
SELECT
a.CLIENTNO
,a.IDNO
,COUNT(*) AS [COUNT]
FROM
#TEST a
,#TEST b
WHERE SUBSTRING(a.IDNO,1,10) AS IDNO = SUBSTRING(b.IDNO,1,10) AS IDNO
AND a.CLIENTNO <> b.CLIENTNO
GROUP BY a.CLIENTNO,a.IDNO
HAVING COUNT(*) > 1

[Rawhide]

Hold on .... I'm rewriting my post .....

[Rawhide]

Try this:

--SELECT ALL IDS INTO TEST TABLE
SELECT CLIENTNO,ID INTO #TEST FROM ADATA

--SELECT THE IDS THAT HAVE A DIFFERENT CLIENT NUMBER
Select IDNO, IDNO10, ClientNo, theCount
From #test c
Inner Join (SELECT Left(a.IDNO, 10) As IDNO10, Count(*) As theCount)
FROM
#TEST a
,#TEST b
WHERE Left(a.IDNO, 10) = Left(b.IDNO, 10)
AND a.CLIENTNO <> b.CLIENTNO
GROUP BY Left(a.IDNO, 10)
HAVING COUNT(*) > 1) As d on d.IDNO10 = Left(c.IDNO, 10)