4

I have a table with a field for a member number. The table can contain records with duplicate values for this field and does.

I have a SQL query that will obtain any duplicate values for this field, however, I would now like to find records where there is another record with the same member number but only the first 8 digits out of the 9 that there are.

For example, I would like to return the below when the query is run;

MEMBNO
123456789
123456782

At the moment, my query only returns;

MEMBNO
123456789

where there are two or more records with this exact number.

Current query is;

SELECT
  basic.MEMBNO
FROM
  basic
GROUP BY
  basic.MEMBNO
HAVING
  COUNT(basic.MEMBNO) >1
ORDER BY
  basic.MEMBNO

A query I thought might work was;

SELECT
  basic.MEMBNO
FROM
  basic
GROUP BY
  basic.MEMBNO
HAVING
  COUNT(LEFT(basic.MEMBNO,8)) >1
ORDER BY
  basic.MEMBNO

I'm faily new to SQL and databases in general, so apologies if I haven't explained myself that well. Does it require the LIKE operator somewhere?

I'm using Microsoft SQL Server Report Builder 3.0.

8

You can identify the repeated 1->8 substrings using this:

SELECT LEFT(basic.MEMBNO,8)
FROM dbo.basic
GROUP BY LEFT(basic.MEMBNO,8)
HAVING COUNT(*) > 1;

So you can join with that to get the individual values:

;WITH x(m) AS 
(
  SELECT LEFT(basic.MEMBNO,8)
  FROM dbo.basic
  GROUP BY LEFT(basic.MEMBNO,8)
  HAVING COUNT(*) > 1
)
SELECT b.MEMBNO
  FROM dbo.basic AS b
  INNER JOIN x
  ON x.m = LEFT(b.MEMBNO, 8)
  ORDER BY b.MEMBNO;

You can also do it this way (I just wasn't sure when windowed COUNT was introduced):

;WITH x AS 
( 
  SELECT MEMBNO, c = COUNT(*) OVER (PARTITION BY LEFT(MEMBNO, 8))
  FROM dbo.basic
)
SELECT MEMBNO FROM x WHERE c > 1
ORDER BY MEMBNO;
  • Hi Aaron, I have left work for the day but will try and implement this tomorrow. It looks promising and also something I will need to look into learning. I will update you on the outcome.Thanks! – Boltie Nov 25 '15 at 18:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.