Solved

Need help with a Qry's where clause

Posted on 2009-04-09
5
224 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
[X]
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
  • 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

726 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