Solved

SQL question

Posted on 2008-06-09
5
190 Views
Last Modified: 2010-03-19
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
Comment
Question by:thenrich
  • 3
5 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 21746222
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
 
LVL 8

Assisted Solution

by:sbagireddi
sbagireddi earned 150 total points
ID: 21746231
SELECT SecondItemNumber,
 COUNT(SecondItemNumber) AS NumOccurrences
FROM Table
GROUP BY SecondItemNumber
HAVING ( COUNT(SecondItemNumber) > 1 )
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 150 total points
ID: 21746232
select * from urTable
where SecondItemNumber in (select SecondItemNumber from urTable Group by SecondItemNumber having count(*) > 1 )  
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21746239
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21746240
note: in any case, a index on the field secondItemnumber will be required for a good performance...
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now