Neha 1 avatar image
Neha 1 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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
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?
4 Likes 4 ·
Scot Hauder avatar image Scot Hauder commented ·
Your query should work, is there more to it that you are not posting?
0 Likes 0 ·
Neha 1 avatar image Neha 1 commented ·
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.
0 Likes 0 ·
Neha 1 avatar image Neha 1 commented ·
thanks, i was using a recursive CTE before this.i resolved it.
0 Likes 0 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
Would you mind posting how you solved it in the end? Also, remember that you can mark your own answer as accepted.
0 Likes 0 ·
Show more comments

0 Answers