Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Need help with a Qry's where clause

Posted on 2009-04-09
5
223 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

790 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