indy500fan
asked on
Using Top, but not as the first field in a qry
Friends,
In a nutshell, I want to get the TOP 4 sectionTimes FOR EACH ResultitemID in my table.
Here is the objective:
Select ResultItemID, TOP 4 (SectionTime) From SectionTimes WHERE SectionID=-1 and SectionTime > -1
Obviously I can't do that.
I could run some vb code and get all the pertinant ResultItemID, then do individual qry's as Select Top 4 SectionTime From SectionTimes Where ResultItemID = X
I'm hoping there is a more efficient way!
Thanks in advance!
In a nutshell, I want to get the TOP 4 sectionTimes FOR EACH ResultitemID in my table.
Here is the objective:
Select ResultItemID, TOP 4 (SectionTime) From SectionTimes WHERE SectionID=-1 and SectionTime > -1
Obviously I can't do that.
I could run some vb code and get all the pertinant ResultItemID, then do individual qry's as Select Top 4 SectionTime From SectionTimes Where ResultItemID = X
I'm hoping there is a more efficient way!
Thanks in advance!
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks AngelIII!
I gave dportas some points because I learned something new with the Cross Apply. I hope you don't mind!
I gave dportas some points because I learned something new with the Cross Apply. I hope you don't mind!
from SectionTimes st_outer
where SectionTime in (
select TOP 4 SectionTime from SectionTimes st_inner
where SectionID=-1 and SectionTime > -1
and st_inner.ResultTimeId = st_outer.ResultTimeId
order by SectionTime desc
)
Imran