>Now that I have a working SQL which groups by a field and also returns a related datetime field, I am trying to know if what I need to achieve is possible. Basically, the current SQL is as follow:
>
>
>SELECT TOP 10 Event.RO_No,Min(Event.DateTime) AS LastUpdate
> FROM Event
> GROUP BY Event.RO_No
> ORDER BY MIN(Event.Datetime)
>
>
>So, this returns something like this:
>
>
>Ro_No DateTime
> 1 2007-12-17 14:41:25.704
> 3 2007-12-17 14:42:25.704
> 7 2007-12-17 14:43:25.704
> 22 2007-12-18 15:41:25.704
> 23 2007-12-19 16:41:25.704
> 24 2008-12-20 07:41:25.704
> 25 2009-12-17 08:41:25.704
> 46 2009-12-17 05:41:25.704
> 48 2009-12-18 05:41:25.704
> 49 2009-12-19 14:41:25.704
>
>
>From that SQL, I need to establish a relation into the RO table. This is accomplished by doing this:
>
>
>SELECT RO.FirstName
>FROM (SELECT TOP 10 Event.RO_No,Min(Event.DateTime) AS LastUpdate
> FROM Event
> GROUP BY Event.RO_No
> ORDER BY MIN(Event.Datetime)) Temp
>INNER JOIN RO ON Temp.RO_No=RO.RO_No
>
>
>So far so good, now the problem I am facing is that this log contains some old archive. So, not all RO_No are present in the RO table. Basically, the RO table is containing some recent records only. But, I need to start from the log. So, the goal here is trying to know if it is possible to make sure to collect the first 10 records which would also be records that exist in RO.
You will need to use EXISTS criteria, e.g.
SELECT TOP 10 Event.RO_No,Min(Event.DateTime) AS LastUpdate
FROM Event
where exists (select 1 from RO where RO.Ro_No = Event.Ro_No)
GROUP BY Event.RO_No
and then join with the RO the same way.
ORDER BY MIN(Event.Datetime
If it's not broken, fix it until it is.
My Blog