Solved

Need help with a Qry's where clause

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now