do not include duplicate

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.
LVL 1
rgb192Asked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
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
 
rgb192Author Commented:
thanks
0
All Courses

From novice to tech pro — start learning today.