Solved

Need help with a Qry's where clause

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Isolation level in SQL server 3 47
Grid querry results 41 73
Selection from table2 where criteria for table1 10 34
create insert script based on records in a table 4 16
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…
In this article I will describe the Copy Database Wizard 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.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

864 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

23 Experts available now in Live!

Get 1:1 Help Now