Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

Need help with a Qry's where clause

Friends,

I have the following qry (attached), but It isn't quite right.

I need to get the information for each resultItemID, contained in each of their Max(Lap).

For example,  I have 1 competitor with a Max Lap of 11, and then another with a Max lap of 8.

With that said, this qry gives me all section information for every lap for every competitor.  Instead, I want only the section information for a competitor in his highest lap.

For example, here is sample data:
3      1      459788061      459821690      0      S1      139.287249370808      33629      1
3      1      459821690      459984565      1      S2      86.5275936649689      162875      1
3      1      459984565      460253890      2      S3      81.3903445482393      269325      1
3      2      460529438      460562753      0      S1      140.600057304245      33315      2
3      2      460562753      460730516      1      S2      84.0064961772371      167763      2
3      2      460730516      460999891      2      S3      81.3752372917106      269375      2
...
5      1      459833325      459866250      0      S1      142.26547939532      32925      1
5      1      459866250      460012288      1      S2      96.5035252344035      146038      1
5      1      460012288      460272967      2      S3      84.0898367166306      260679      1
5      2      460533411      460567013      0      S1      139.399169962827      33602      2
5      2      460567013      460718521      1      S2      93.0193905152323      151508      2
5      2      460718521      460980400      2      S3      83.7045144721591      261879      2
...

And What I should be seeing is:

3      8      466978324      467011899      0      S1      139.511270561159      33575      8
3      8      467011899      467162223      1      S2      93.7520410458863      150324      8
3      8      467162223      468088309      2      S3      23.6699988396915      926086      8
5      10      468134326      468193788      0      S1      78.7745267412954      59462      10
5      10      468193788      468407528      1      S2      65.9360990838487      213740      10
5      10      468407528      468793718      2      S3      56.76080309033      386190      10

Any Suggestions?

Best Regards,
Eric
Select r.No, Max(st.Lap) as MaxLap, st.EntryTime, st.ExitTime, st.SectionID, 
	st.ShortName, st.SectionSpeed, st.SectionTime, st.Lap
From
SectionTimes st inner join Results r
ON st.RunID=r.RunID AND st.ResultItemID=r.ResultID
inner join SectionPreferredOrder spo
ON st.SectionID=spo.SectionID
 
Where st.ShortName Like 'S%' and st.Lap >0 and st.Lap < 65000
and st.Lap IN (Select (s.Lap) 
	From SectionTimes s Where s.RunID=st.RunID 
		AND s.ResultItemID=st.ResultItemID
		AND s.Lap < 65000)
Group By r.No, st.Lap, st.ResultItemID,  st.ShortName, st.SectionID, st.SectionSpeed, st.EntryTime, st.ExitTime, st.SectionTime
Order By st.ResultItemID, st.Lap, st.SectionID
 
                                      

Open in new window

0
indy500fan
Asked:
indy500fan
  • 3
  • 2
1 Solution
 
reb73Commented:
Try -
Select r.No, st.Lap, st.EntryTime, st.ExitTime, st.SectionID, 
        st.ShortName, st.SectionSpeed, st.SectionTime, st.Lap
From
	SectionTimes st
inner join Results r
	ON st.RunID=r.RunID AND st.ResultItemID=r.ResultID
inner join SectionPreferredOrder spo
	ON st.SectionID=spo.SectionID
Where st.Lap IN (Select max(s.Lap) 
        From SectionTimes s Where s.RunID=st.RunID 
                AND s.ResultItemID=st.ResultItemID
                AND s.Lap < 65000
		AND st.ShortName Like 'S%')
Order By st.ResultItemID, st.Lap, st.SectionID

Open in new window

0
 
indy500fanAuthor Commented:
That is pretty close to what I had originally; however, when I run this I have no records in my dataset.
0
 
reb73Commented:
OK, how about this?
Select r.No, st.Lap as MaxLap, st.EntryTime, st.ExitTime, st.SectionID, 
        st.ShortName, st.SectionSpeed, st.SectionTime, st.Lap
From
	SectionTimes st
inner join Results r
	ON st.RunID=r.RunID AND st.ResultItemID=r.ResultID
inner join SectionPreferredOrder spo
	ON st.SectionID=spo.SectionID
Where exists (Select max(s.Lap) 
        	From SectionTimes s
		Where s.RunID=st.RunID
                AND s.ResultItemID=st.ResultItemID
		AND s.SectionId = spo.SectionID
		AND s.RunId = r.RunId
		AND s.ResultItemId = r.ResutlID
		AND s.ShortName Like 'S%' and s.Lap > 0 and s.Lap < 65000
		)
Order By
	st.ResultItemID, st.Lap, st.SectionID

Open in new window

0
 
indy500fanAuthor Commented:
Actually, what I found is that I had to do a -1 on the MaxLap to get any data.  Your original solution is what I was looking for, I just didn't know it.  :)

Thanks Eric!
0
 
reb73Commented:
No worries.. Cheers!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now