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
