Hi can anyone give me their 2 cents in this query. I'm am to delete some rows on my table and i am supposed to retain only the last 2 updates (timestamp) for every employee number. However i found out that for every 1 employee number, there are as much as 20k rows of updates and when i count the timestamp, there were several records having the same timestamp.

here's my query to get the last 2 updates which i would eventually convert into a delete statement :

select empno, last_update
from table a
where 2 > (select count(*)
where a.last_update < last_update)


however, i got this excerpt from the SQL reference about DELETE statement that says:
"If the search condition contains a subquery, the subquery can be thought of as being executed each time the search condition is applied to a row, and the results used in applying the search condition. In actuality, a subquery with no correlated references is executed just once, whereas it is possible that a subquery with a correlated reference must be executed once for each row.
Let T2 denote the object table of a DELETE statement and let T1 denote a table that is referred to in the FROM clause of a subquery of that statement. T1 must not be a table that can be affected by the DELETE on T2. Thus, the following rules apply:
 T1 and T2 must not be the same table.

If this is the case, then I say that the delete statement will not work. I cant think of another queryt that would eventually DELETE all the other rows except the last 2 updates. Can anyone tell me if there's another way or the only way, I could do this is to create a COBOL program?

Your help is very much appreciated. FYI. i am using DB2 7 for OS/390

Hi starra,

I don't see a problem with a query like

delete from table a where 2 > ( select count(*) where a.last_update < last_update )

If it doesn't work, you should file a PMR with IBM support. From my point of view, it is perfectly valid SQL.

Johann

hi Johann,

Thanks for your reply. the query should work, i did try it on other database and it works perfectly, however, when i ran it against my data, it did not return any rows. upon looking at the data, i saw that there are several rows with exactly the same timestamp. and right now that is my dilemma.

Hi starra,

well, if you can prove it, do not hesitate and file a complaint with IBM support.

Johann