A B 1 a 2 b 3 c 2 b 1 a 4 dAlso, I want to insert a record using a single query that will also check for duplicates, and only if there is no duplicate record, then it should insert.
Another question: I have a table with two fields: BankName and AccountHolder.
BankName AccountHolder a1 x1 a1 y1 a1 z1 b1 x1 b1 z1 c1 x1 c1 y1 c1 z1 c1 w1I have to find out the bank name with the second maximum account holders.
First off, you should have constraints on that table in the form of a primary key that does not allow duplicates. That avoids the problem in the first place. There isn't a single, one step query that will both identify and delete only the duplicate rows in that table leaving a single copy. The most straightforward way is to do the following:
Select distinct col1, col2 Into #temp >From orginaltable Delete from originaltable Insert into originaltable Select col1, col2 >From #tempFor the second question, look up "not exists" in BOL. I don't understand what a second maximum account holder is, so I don't know how to answer that question.
For More Information
- Dozens more answers to tough SQL Server questions from Michael Hotek are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.