This question was
closed
by
Fatherjack for the following reason:
asked
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.
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?
Your query should work, is there more to it that you are not posting?
Would you mind posting how you solved it in the end? Also, remember that you can mark your own answer as accepted.