well - that was what I tried first - but that does not work because that returns the latest date for which the task_id has a record as Assignment. (It is like it picks up the max date from all records that have name_rec_type as Assignment)
But I wanted was if the date corresponding to the name_rec_type as Assignment is the max(date) then return that task_id. I hope I have made myself clear... Thanks, Anoop On 6/2/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > Hi, > Try just : > > SELECT id_secr_rqst task_id, MAX(dt_aud_rec) AS latest > FROM isr2_aud_log WHERE > name_rec_type = 'Assignment' AND id_secr_rqst > ='TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF' > GROUP BY id_secr_rqst > ; > > +------------------------------------------------+---------------------+ > | task_id | latest | > +------------------------------------------------+---------------------+ > | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:39:29 | > +------------------------------------------------+---------------------+ > 1 row in set (0.02 sec) > > Mathias > > > Selon Anoop kumar V <[EMAIL PROTECTED]>: > > > 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_recAS 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