subject:"Need help in basic query"

Re: Need help in basic query

2005-06-03 Thread Anoop kumar V

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

Need help in basic query

2005-06-02 Thread Anoop kumar V

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

Re: Need help in basic query

2005-06-02 Thread Anoop kumar V

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

Re: Need help in basic query

2005-06-02 Thread Anoop kumar V

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

2005-06-02 Thread mfatene

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

Need help in basic query

Re: Need help in basic query

Re: Need help in basic query

Re: Need help in basic query

5 matches

Site Navigation

Mail list logo

Footer information