Solved

do not include duplicate

Posted on 2011-09-19
2
242 Views
Last Modified: 2012-05-12
use same table structure as related question

want all the columns for duplicate "dept_name"
   in the dept table.  do not include original row,  Only  include the duplicate row.
  the original row is the row that is entered first.
0
Comment
Question by:rgb192
2 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36563781
If you want to use the dept_id that is the lowest as the original row, you can consider ROW_NUMBER() windowing function in SQL 2005.

SELECT {columns you want in final select}
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY dept_name ORDER BY dept_id) RN
FROM dept
) derived
WHERE RN > 1
;

You will get the duplicate rows. The original row is RN = 1, so those will be excluded.

Good luck!
0
 

Author Closing Comment

by:rgb192
ID: 36564733
thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 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

18 Experts available now in Live!

Get 1:1 Help Now