Solved

Need help with a Qry's where clause

Posted on 2009-04-09
5
222 Views
Last Modified: 2012-05-06
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
Comment
Question by:indy500fan
  • 3
  • 2
5 Comments
 
LVL 25

Accepted Solution

by:
reb73 earned 500 total points
ID: 24110181
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
 

Author Comment

by:indy500fan
ID: 24110208
That is pretty close to what I had originally; however, when I run this I have no records in my dataset.
0
 
LVL 25

Expert Comment

by:reb73
ID: 24110325
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
 

Author Comment

by:indy500fan
ID: 24110340
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
 
LVL 25

Expert Comment

by:reb73
ID: 24110388
No worries.. Cheers!
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question