Sub Queries

[djburnheim]

Hi I'm very new to informix databases although have a bit of experience with SQL and have managd to put together the below query that returns a maximum for me. I'm now trying to workout out how to find the time that the maximum occured which is stored in the idrecord.IntialStart column.

Query to find max
SELECT max((agerecord.Secondstart -idrecord.Intialstart) + int2interval(agerecord.Length))
FROM agerecord, idrecord
WHERE agerecord.id = idrecord.id
AND ((idrecord.Intialstart Between {ts '2003-06-02 00:00:00'} And {ts '2003-06-03 00:00:00'})

I thought the query to find the time that it occured would be something like:-

SELECT idrecord.IntialStart
FROM agerecord, idrecord
WHERE (SELECT max((agerecord.Secondstart -idrecord.Intialstart) + int2interval(agerecord.Length))
FROM agerecord, idrecord
WHERE agerecord.id = idrecord.id
AND ((idrecord.Intialstart Between {ts '2003-06-02 00:00:00'} And {ts '2003-06-03 00:00:00'})) = (SELECT max((agerecord.Secondstart -idrecord.Intialstart) + int2interval(agerecord.Length))
FROM agerecord, idrecord
WHERE agerecord.id = idrecord.id
AND ((idrecord.Intialstart Between {ts '2003-06-02 00:00:00'} And {ts '2003-06-03 00:00:00'}))

And this does seem to return the right time but it returns it for every row in the table. I added Distinct to the query and the query then took absolutely ages and returned an error "unable to write rows"

Any suggestions?

thanks
Dave