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

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

---

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

---

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

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 ! ;)

---

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

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 !

---