OK - I have found the cause of the inconsistency - Whenever I have more than one record which has name_rec_type as 'Assignment' I do not get any results (I get an empty result) But if I have only one Assignment record then it returns the correct row.
Question is: How can I overcome this - I just need the id_secr_rqst if and only if it has the latest name_rec_type as 'Assignment' - I do not care what the earlier records contained. help please, Anoop On 6/2/05, Anoop kumar V <[EMAIL PROTECTED]> wrote: > > Hi mysql-ers, > > I need help in a basic query: > > I have this table: > > select * from isr2_aud_log where > id_secr_rqst IN ('TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF') > --and name_rec_type = 'Assignment' > order by id_secr_rqst, dt_aud_rec > > > +------------------------------------------------+---------------------+---------------------+ > | id_secr_rqst | dt_aud_rec | name_rec_type | > > +------------------------------------------------+---------------------+---------------------+ > | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | > Submission | > | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | > Exception Requested | > | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | > Exception Resource | > | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:33:22 | > Director Approval | > | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:34:43 | > Assignment | > | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:35:03 | > Risk Assessment | > | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:36:03 | > Assignment | > | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:37:40 | > SERB Approval | > | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:39:29 | > Assignment | > > +------------------------------------------------+---------------------+---------------------+ > > and i am using this query: > > SELECT t1.id_secr_rqst task_id, MAX(t2.dt_aud_rec) AS latest, > t1.dt_aud_rec AS date1 > FROM isr2_aud_log t1, isr2_aud_log t2 WHERE t1.id_secr_rqst = > t2.id_secr_rqst > AND t1.name_rec_type = 'Assignment' AND > t1.id_secr_rqst IN ( > 'TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF') > GROUP BY t2.id_secr_rqst HAVING date1 = latest > > What I expected to get is the id_secr_rqst which has the last > name_rec_type = 'Assignment' > In this case there is only one id_secr_rqst and it has the last > name_rec_type as 'Assignment'. But I do not seem to get consistent results. > As I am using an older version of mysql I do not have the liberty to use > subqueries and will have to do everything using joins only. > > The problem I am facing is that this query only sometimes returns rows and > most of the time I get an empty result set. This table does not have any > primary keys. > > Can somebody please point out what is the mistake I am doing - I think it > just needs a tweak here and there (I hope..) > > Thanks, > Anoop > > -- > Thanks and best regards, > Anoop -- Thanks and best regards, Anoop