Re: query help wanted

Sandeep Deshmukh wrote:
>
> Ok, I figured one out:
>
> delete from atab where custid = <blah> and
> rownum < (select (max(rownum) -200) from atab where custid = <blah>);

This assumes that the rows in the table are stored in a particular order - specifically in ascending date order. You cannot assume this. This may work if the rows HAPPEN to be stored in that order - and they just might be. But you will be basing your procedure on a something that is just not guaranteed and, if used long enough, this statement will probably delete the wrong data.

Unless there is some ban on PL/SQL for this application, just do the following, it will always work the way you want:

DECLARE
  theCount NUMBER;
  VcustID NUMBER;
  VaccDate DATE;
  VrowCnt NUMBER;
  Vrowid ROWID;

  CURSOR custCurs IS
    SELECT

       custID,
       count(*)
    FROM
       custAccess
    GROUP BY
       custID
    HAVING
       count(*) > 200;

  CURSOR accDTCurs IS
    SELECT

      access_date,
      rowid
    FROM
      custAccess
    WHERE
      custID = VcustID
    ORDER BY
      access_date DESC,
      rowid DESC;
       

BEGIN   OPEN custCurs;
  LOOP
    FETCH custCurs INTO VcustID, VrowCnt;     EXIT WHEN custCurs%NOTFOUND;

    theCount := 0;

    OPEN accDTCurs;
    LOOP

      FETCH accDTCurs INTO VaccDT, Vrowid;
      EXIT WHEN accDTCurs%NOTFOUND OR theCount >= 200;

      theCount := theCount + 1;

    END LOOP;
    CLOSE accDTCurs;

    IF theCount = 200 THEN

       DELETE
       FROM
         custAccess
       WHERE
         access_date < VaccDT
         OR (access_date = VaccDT AND rowid < Vrowid);
         

    END IF;        END LOOP;
  CLOSE custCurs;
  COMMIT;
END;
..
/
exit

If I made any typos, sorry. But you get the idea. This also handles cases where a particular customer had multiple records with identical access_date values.

You may want to do periodic commits instead of one at the end if this will be deleting thousands and thousands of records.

• TRW -- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| | Tim Witort || He is not silent. He is not | | trwATmedicalertDOTorg || whispering. We are not quiet | | || we are not listening. | | Pin: TZ, only one :^( || - Out of the Grey | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||