Hello Mahesh,

Am Samstag, 24. Januar 2004 09:22 schrieb Mahesh Ishwar:

> 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 | --X--
> | O9041e0fc:fa31fe8fcf:dad | A | 2004-01-20 07:37:04 | --X--
> | 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'.

--X-- What about the above both rows? The timestamp is greater and sthe state
is not C.

>
> 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'?

Try
SELECT FIRST 1 hrod_ids FROM tablename ORDER BY hrod_time_stamp.

Walter.