Duplicate records and row_number
672252Sep 8 2011 — edited Sep 9 2011Hi,
Been trying to delete duplicate records. The code below works, but would delete everything, rather than just the > #1 records:
(SELECT academic_period, load_week, sub_academic_period, person_uid, course_number,
course_reference_number, rowid as rid, row_number() over (partition by
academic_period, load_week, sub_academic_period, person_uid, course_number,
course_reference_number order by academic_period, load_week, sub_academic_period,
person_uid, course_number, course_reference_number)
FROM cea
WHERE (academic_period, load_week, sub_academic_period, person_uid, course_number,
course_reference_number) IN
(SELECT academic_period, load_week, sub_academic_period, person_uid, course_number,
course_reference_number
FROM cea
GROUP BY academic_period, load_week, sub_academic_period, person_uid, course_number,
course_reference_number
HAVING COUNT(*) > 1) )
if I attempt to put "as rn" and rn > 1, I get ora-00933: Sql command not properly ended
SELECT academic_period, load_week, sub_academic_period, person_uid, course_number,
course_reference_number, rowid as rid, row_number() over (partition by
academic_period, load_week, sub_academic_period, person_uid, course_number,
course_reference_number order by academic_period, load_week, sub_academic_period,
person_uid, course_number, course_reference_number) as rn
FROM cea
WHERE (academic_period, load_week, sub_academic_period, person_uid, course_number,
course_reference_number) IN
(SELECT academic_period, load_week, sub_academic_period, person_uid, course_number,
course_reference_number
FROM cea
GROUP BY academic_period, load_week, sub_academic_period, person_uid, course_number,
course_reference_number
HAVING COUNT(*) > 1)
and rn > 1
I tried removing "as rn" and doing 'rn' and "rn", that gave me a syntax error also. The "and rn > 1" clause gets a syntax error also. I've reviewed a bunch of different websites. All of this indicate the syntax I've been using will work. However, any query I run in TOAD, always errors out when I include the reference "rn > 1".
Any ideas? Thanks!
Victoria