hi All, I need to create a query using no subqueries as I use 4.0.23 which does not support subqueries. I cannot upgrade for some compellimg reasons (the product does not support anything later than 4.0.23nt as of now). I also cannot use any thing that is native to mysql - in the sense that the query should be as generic/simple as possible so that I can run it against both DB2 or Sybase.
Here is the problem.. I have this table: (modified to simplify) +------------------------------------------------+ | id_secr| name_rec_type | dt_aud_rec | +------------------------------------------------+ | TASKD1 | Risk Assessment | 2005-05-20 19:07:54 | | TASKD1 | Assigned | 2005-05-20 19:07:53 | | TASKD1 | Pending | 2005-05-20 12:10:50 | | TASKD2 | Closed | 2005-05-20 19:06:27 | | TASKD2 | Risk Assessment | 2005-05-20 19:06:04 | | TASKD2 | Pending | 2005-05-20 19:05:54 | | TASKD3 | Closed | 2005-05-20 16:40:14 | | TASKD3 | Risk Assessment | 2005-05-20 10:07:54 | | TASKD3 | Assigned | 2005-05-20 10:00:54 | | TASKD4 | Closed | 2005-05-20 10:34:13 | | TASKD4 | Risk Assessment | 2005-05-20 09:07:54 | | TASKD4 | Assigned | 2005-05-20 09:00:54 | | TASKD4 | Assigned | 2005-05-20 09:00:04 | | TASKD5 | Closed | 2005-05-20 15:33:13 | | TASKD5 | SERB Assessment | 2005-05-20 15:07:54 | | TASKD5 | Assigned | 2005-05-20 14:07:54 | | TASKD5 | Risk Assessment | 2005-05-20 13:07:54 | | TASKD5 | Risk Assessment | 2005-05-20 12:07:54 | | TASKD6 | Closed | 2005-05-20 14:18:28 | | TASKD6 | Risk Assessment | 2005-05-20 13:07:54 | | TASKD6 | Assigned | 2005-05-20 12:07:54 | | TASKD6 | Pending | 2005-05-20 11:07:54 | | TASKD6 | Pending | 2005-05-20 10:07:54 | | TASKD6 | Pending | 2005-05-20 09:07:54 | | TASKD6 | Pending | 2005-05-20 08:07:54 | | TASKD6 | Pending | 2005-05-20 07:07:54 | +------------------------------------------------+ what I need is to pull out data based on the latest two dates. I will be checking the status (name_rec_type) and if my status matches any one of the latest 2 name_rec_type I will do some processing. The only thing compounding this is that I cannot use any sub queries - I can do as many joins as necessary. Also the query need not be performance intensive as I dont think we will have more than 2000 rows at any time. Moreover I will have to ignore all rows (or wholes TASKD*'s) where the name_rec_type is closed anywhere. here is what I have come up so far - but it gives only the latest data: (and not the last 2 latest) select t1.id_secr_rqst, t2.name_rec_type, max(t1.dt_aud_rec) from isr2_aud_log t1, isr2_aud_log t2 where t1.id_secr_rqst = t2.id_secr_rqst and t1.name_rec_type='Exception Resource' group by t1.id_secr_rqst I need help. Thanks in advance. Anoop
