SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Max Datetime returning multiple results
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Anthem
Starting Member

5 Posts

Posted - 10/23/2013 :  16:53:36  Show Profile  Reply with Quote

Hi, hope someone can help. I have a query running without issue on around 100,000 results however i have a couple where my MAX aggregate is returning two results for a MAX(date time) and i cannot figure out why. I have paired down the query to try and work it out but still don't know.

my query now looks like this (i have remove most columns)

SELECT STATUS, RunOn, sort, T_ID
FROM dbo.Results
WHERE (RunOn IN
(SELECT MAX(RunOn)
FROM dbo.Results
group BY T_ID) AND T_ID = 21405)

ORDER BY sort

I added the T_ID = 21405 to restrict the results to the problematic results, if i set T_ID to any other test result i get one value based on the latest RunOn, but for some reason with this T_ID i get two values, the latest one and one slightly older... i am lost as to why, please help. The date time looks ok, i just can't work out why the latest and one older result are returned !
The original qury was grouping by more items but i removed those to try and work out what is going one, as mentioned for 99.9% of results it works perfectly, but for this one i don't know.


Thanks a lot !

bitsmed
Constraint Violating Yak Guru

277 Posts

Posted - 10/23/2013 :  17:16:54  Show Profile  Reply with Quote
try this

select a.status
      ,a.runon
      ,a.sort
      ,a.t_id
  from dbo.results as a
       inner join (select t_id
                         ,max(runon) as runon
                     from dbo.results
                    group by t_id
                  ) as b
               on b.t_id=a.t_id
              and b.runon=a.runon
 where a.t_id=21405
 order by a.sort
Go to Top of Page

Anthem
Starting Member

5 Posts

Posted - 10/23/2013 :  17:22:22  Show Profile  Reply with Quote
thanks a lot , why would my version not work ?, could you explain what are you doing in the above example ?, i can't fathom why it works 99% of the time for a simple MAX(Datetime)

Many thanks
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

277 Posts

Posted - 10/23/2013 :  17:35:11  Show Profile  Reply with Quote
quote:
Originally posted by Anthem

thanks a lot , why would my version not work ?, could you explain what are you doing in the above example ?, i can't fathom why it works 99% of the time for a simple MAX(Datetime)

Many thanks


Your inner select returns max runid for each t_id. Now, t_id 21405 must have several runid's matching the max runid's returned, and this is why you get more than one result.
In other Words, your query matches max runid's across all t_id's.

My suggestion matches on both t_id and runid.
Go to Top of Page

Anthem
Starting Member

5 Posts

Posted - 10/23/2013 :  17:52:31  Show Profile  Reply with Quote
Again thanks for replying, i am confused as i am using MAX(RunOn) which is a datetime, so it should return the most recent ?, they are both different dates and times, 2 days apart, but it returns both, should MAX not just return the newest ?. You are right that there are many runs under 21405, but each one has a unique datetime, so i am expecting just the newest one ?, sorry for being a bit slow if this is obvious

the RunOn values are :-

14/10/20013-09:30:01
16/10/2013-12:25:05

Should the 16/10 not be returned ?

Thanks again
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

277 Posts

Posted - 10/23/2013 :  18:11:24  Show Profile  Reply with Quote
quote:
Originally posted by Anthem

Again thanks for replying, i am confused as i am using MAX(RunOn) which is a datetime, so it should return the most recent ?, they are both different dates and times, 2 days apart, but it returns both, should MAX not just return the newest ?. You are right that there are many runs under 21405, but each one has a unique datetime, so i am expecting just the newest one ?, sorry for being a bit slow if this is obvious

the RunOn values are :-

14/10/20013-09:30:01
16/10/2013-12:25:05

Should the 16/10 not be returned ?

Thanks again


Try searching for runid's = 14/10/2013-09:30:01
My guess is, this runid is the max runid, for another t_id.
Now if you would like to focus your select only on t_id 21405, your sql could be like this

select status
      ,runon
      ,sort
      ,t_id
  from dbo.results
 where runon in (select max(runon)
                   from dbo.results
                  where t_id=21405
                )
   and t_id=21405
 order by sort
Go to Top of Page

Anthem
Starting Member

5 Posts

Posted - 10/23/2013 :  18:22:12  Show Profile  Reply with Quote
You are right !, i have searched and there is another test with exactly the same time stamp but under a different t_id !, can you explain how this would affect my requests for results just for 21405 ?

Thanks a lot !, i think i can see light at the end of the tunnel ! ;)
Go to Top of Page

Anthem
Starting Member

5 Posts

Posted - 10/23/2013 :  18:33:17  Show Profile  Reply with Quote
I have looked into it and you are right, the timestamp matches another test so it is being included !, thanks so much, really appreciate it !, i was going mad !, so to get round that problem, is your suggestion at the top the best way forward ?

Thanks again !
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

277 Posts

Posted - 10/23/2013 :  18:39:59  Show Profile  Reply with Quote
quote:
Originally posted by Anthem

You are right !, i have searched and there is another test with exactly the same time stamp but under a different t_id !, can you explain how this would affect my requests for results just for 21405 ?

Thanks a lot !, i think i can see light at the end of the tunnel ! ;)


Using the latest sql I gave you, will return only one row, but you can only focus on one t_id. Using the first sql I gave you, will do exactly the same (returning one row per t_id), but you will be able to focus on multiple/all t_id's.
I know which one I would chose, if I were the one to decide.
Now, you could speed up the first sql I gave you, if you are focusing on one/a few t_id's, like this

select a.status
      ,a.runon
      ,a.sort
      ,a.t_id
  from dbo.results as a
       inner join (select t_id
                         ,max(runon) as runon
                     from dbo.results
                    where t_id in (21405,21406)
                    group by t_id
                  ) as b
               on b.t_id=a.t_id
              and b.runon=a.runon
 order by a.sort
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

277 Posts

Posted - 10/23/2013 :  18:44:58  Show Profile  Reply with Quote
quote:
Originally posted by Anthem

I have looked into it and you are right, the timestamp matches another test so it is being included !, thanks so much, really appreciate it !, i was going mad !, so to get round that problem, is your suggestion at the top the best way forward ?

Thanks again !


I don't know if it the best way, but it's the best way of the two mentioned. I bet some of the experts in here will be able to come up with even better ways. I'm curious to see what they suggest.

Edited by - bitsmed on 10/23/2013 18:45:47
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
946 Posts

Posted - 10/23/2013 :  22:00:05  Show Profile  Reply with Quote

select
	a.status
,	a.runon
,	a.sort
,	a.t_id
from dbo.results as a
where runon = (select max(runon) from dbo.results as b where a.t_id = b.t_id)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000