SQL tuning help

[gandolf989]

I am writing some code which will ultimately flag duplicate rows. The rows are duplicate based on these fields being the same grp_cl_n, pr_typ_c as well as the date field being in the same month (slegs_beg_dt). This code seems to come back with the correct rows. I was wondering if there is an easier way to do this. Using the WHERE EXISTS or WHERE NOT EXISTS does not seem to work. For some reason the oldest row in the table is the one we want to keep. If this doean't make any sense to you, don't worry it doesn't make any sense to me either.

Thanks in advance.

SELECT dregs_rec_id
  FROM dregs
 WHERE (
           grp_cl_n, pr_typ_c, TRUNC(dregs_beg_dt)) IN
  ( SELECT grp_cl_n, pr_typ_c, TRUNC(dregs_beg_dt)
      FROM dregs ddregs
     WHERE dregs_est_y_m_dt = '200401'
     GROUP BY grp_cl_n,
              pr_typ_c,
              TRUNC(dregs_beg_dt)
    HAVING COUNT(*) > 1) AND
    (grp_cl_n, pr_typ_c, sec_id) NOT IN (
       SELECT grp_cl_n, pr_typ_c, MIN(sec_id) sec_id
         FROM dregs
        WHERE grp_cl_n = ddregs.grp_cl_n AND
              pr_typ_c = ddregs.pr_typ_c
        GROUP BY grp_cl_n, pr_typ_c );

[pss]

Is dregs_rec_id column being populated from a monotone increasing sequence? If so and you are not updating your records you can use it to destinguish between old and new records. Just take your first subquery and add max(dregs_rec_id) the the list of columns being selected.

I'll think more about it after I come back from lunch

[pss]

I'm back... and I have a question:
What is the difference between dregs_rec_id and sec_id? What is the PK for this table?

[gandolf989]

dregs_rec_id is the primary key, it is made up of a sequence. sec_id is not a primary key, but I need to get the oldest sec_id since all of the data comes over from another table and the pkey doesn't mean a thing. I was looking through the sql scripts on this site and found a query to copy from that seems cleaner. I then load the rows into a collection and do an update on each row.

I did some checking and the other query doesn't return the correct number of rows.

SELECT a.dregs_rec_id 
  FROM dregs a
WHERE dregs_est_y_m_dt = '200401' AND
      a.rowid > ANY (
   SELECT b.rowid
     FROM dregs b
    WHERE dregs_est_y_m_dt = '200401'                      AND
          b.grp_cl_n            = a.grp_cl_n               AND
          b.pr_typ_c            = a.pr_typ_c               AND
          TRUNC(b.dregs_beg_dt) = TRUNC(a.dregs_beg_dt) )
/