x

[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.
more ▼

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

Neha 1 gravatar image

Neha 1
35 4 7 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)
10|1200 characters needed characters left

The question has been closed Jul 02 '10 at 12:55 PM by Fatherjack for the following reason:


0 answers: sort newest

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1933
x36

asked: Jun 30 '10 at 05:07 PM

Seen: 1240 times

Last Updated: Jul 01 '10 at 01:14 AM