[Closed] MAX Datetime

I am trying to find and compare max (datetime) value for one row in the table using a self join. e.g cte_q stores: ID =1 time='2010-11-8 00:00'

Query is:

Select ID
from cte_q a
where a.time=(select max(b.time) from cte_q b where a.ID=b.ID)

but this is a never ending query in SQL 2005.It keeps on running though there is only 1 record in cte_q.

I don't really know why.any sort of help is appreciated.

asked Jun 30 '10 at 05:07 PM in Default

Neha 1
35 ● 4 ● 5 ● 8

Your query should work, is there more to it that you are not posting?

Jun 30 '10 at 05:36 PM Scot Hauder

nope..thats why i am wondering.Its the same query.It works for >1 record in the cte_q table but if there is only 1 row in cte_q it runs indefinetely.

Jun 30 '10 at 05:42 PM Neha 1

thanks, i was using a recursive CTE before this.i resolved it.

Jun 30 '10 at 09:29 PM Neha 1

Hi Neha. Good that you managed to solve the problem. When I have a look at your query, I see a potential cause to you original problem: To be able to evaluate what's left of the "=", the expression to the right of "=" must be evaluated first - the WHERE clause a.time= must be evaluated to get the ID or ID's from the SELECT clause. BUT.. the right side of "=" depends on ID from the left side of "=". So to me it looks like the main query is waiting for the subquery to finish, while the subquery needs to main query to return a value.

How did you solve it?

Jun 30 '10 at 11:55 PM Magnus Ahlkvist

Would you mind posting how you solved it in the end? Also, remember that you can mark your own answer as accepted.

Jul 01 '10 at 12:04 PM TimothyAWiseman

(comments are locked)