Re: Need help in basic query
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
Re: Need help in basic query
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
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help in basic query
SOLVED:
I changed my query to include max(t1.dt_aud_rec) instead of t1.dt_aud_rec.
I had guessed that it required just a tweak here and there...
Does anybody have any other suggestions apart from this??
Thanks,
Anoop
On 6/2/05, Anoop kumar V <[EMAIL PROTECTED]> wrote:
>
> 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
>
--
Thanks and best regards,
Anoop
Re: Need help in basic query
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
Need help in basic query
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
