Hello All,
Can someone help me in writing a query for the data below

+--------------------------+---+----------------------+
| hrod_id | s | hrod_time_stamp |
+--------------------------+---+----------------------+
| O9041e0fc:fa31fe8fcf:dac | A | 2004-01-20 07:36:51 |
| O9041e0fc:fa31fe8fcf:dad | A | 2004-01-20 07:36:51 |
| O9041e0fc:fa31fe8fcf:dae | A | 2004-01-20 07:36:51 |
| O9041e0fc:fa31fe8fcf:dae | Q | 2004-01-20 07:36:56 |
| O9041e0fc:fa31fe8fcf:dad | Q | 2004-01-20 07:36:56 |
| O9041e0fc:fa31fe8fcf:dac | Q | 2004-01-20 07:36:56 |
| O9041e0fc:fa31fe8fcf:dae | A | 2004-01-20 07:37:00 |
| O9041e0fc:fa31fe8fcf:dac | A | 2004-01-20 07:37:01 |
| O9041e0fc:fa31fe8fcf:dad | A | 2004-01-20 07:37:04 |
| O9041e0fc:fa31fe8fcf:dad | C | 2004-01-20 07:37:49 |
| O9041e0fc:fa31fe8fcf:dac | C | 2004-01-20 07:37:50 |
+--------------------------+---+----------------------+

I have to fetch all hrod_ids with max(hrod_time_stamp) but 's' (state)column is not 'C'.
This means that the query should give only one hrod_id 'O9041e0fc:fa31fe8fcf:dae',with its hrod_time_stamp as '2004-01-20 07:37:00' as it doesn't has a value of 'C' in 's'.

Result :
+--------------------------+---+----------------------+
| hrod_id | s | hrod_time_stamp |
+--------------------------+---+----------------------+
| O9041e0fc:fa31fe8fcf:dae | A | 2004-01-20 07:37:00 |
+--------------------------+---+----------------------+

Making a join with itself, would surely solve it, but I want to avoid joins as the data in the table is voluminous. Can there be a query with some 'where clause' in conjunction with 'having clause'?

Thanx in Advance
Mahesh.


---------------------------------
Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now

[Non-text portions of this message have been removed]