Sub Query 1

[djburnheim]

Hi use the following query to find a maximum..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 (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 (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 (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 returned it for every row in the table. I added Distinct to the query and it seemed to make everything hang.

Any suggestions?

thanks
Dave

 

[JamesLean]

Are you using SQL Server? It doesn't look like it from this query. You should post this in a forum dedicated to your DBMS as answers we give here may not be applicable.

--James

 

[djburnheim]

You're right..it is an informix database but I know more sql and the queries language seems to be much the same. I have posted a query before in the Informix 4GL forum but doesn't seem like it's very popular. I think the only part of the query that wouldn't work on SQL is the in2interval.. I was hoping sub queries would be the same

Dave