current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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.

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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;
share|improve this answer
    
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 yesterday
    
Thanks again Aaron! This worked as expected. – Boltie 11 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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