Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

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

0
thenrich
Asked:
thenrich
  • 3
3 Solutions
 
Guy Hengel [angelIII / a3]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
 
sbagireddiCommented:
SELECT SecondItemNumber,
 COUNT(SecondItemNumber) AS NumOccurrences
FROM Table
GROUP BY SecondItemNumber
HAVING ( COUNT(SecondItemNumber) > 1 )
0
 
Aneesh RetnakaranDatabase 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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now