SQL question

I have a table that has a column called 'SecondItemNumber'

How can I return a result-set that returns all duplicate records where SecondItemNumber = SecondItemNumber

LVL 5
thenrichAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
here we go:
select t.*
  from yourtable t
 where t.SecondItemnumber in ( select i.secondItemnumber from yourtable i group by i.secondItemnumber  having count(*) > 1 ) 

Open in new window

0
 
sbagireddiConnect With a Mentor Commented:
SELECT SecondItemNumber,
 COUNT(SecondItemNumber) AS NumOccurrences
FROM Table
GROUP BY SecondItemNumber
HAVING ( COUNT(SecondItemNumber) > 1 )
0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
select * from urTable
where SecondItemNumber in (select SecondItemNumber from urTable Group by SecondItemNumber having count(*) > 1 )  
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you have a "primary key" or "unique key" field (which will not be secondItemnumber obviously), here a alternative:
select t.*
  from yourtable t
 where exists ( select null from yourtable i where i.secondItemnumber = t.secondItemnumber  and i.that_primary_key <> t.that_primary_key )

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note: in any case, a index on the field secondItemnumber will be required for a good performance...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.