Solved

Need help with a qry, Selecting IN a Where Clause, with a twist.

Posted on 2008-10-02
4
223 Views
Last Modified: 2010-04-21
Friends,

I have the following qry:

Declare @LapSectionID int
Set @LapSectionID = -1
Declare @RunID int
Set @RunID=3

Select t1.ResultItemID, t1.ShortName, t1.Sectiontime, t1.SectionSpeed, t1.Lap
From SectionTimes t1
inner join
(Select top 20 ResultItemID, Lap, SectionTime From
SectionTimes
Where
SectionID=@LapSectionID
AND SectionTime > -1
AND RunID=@RunID
AND
SectionTime IN (Select Min(SectionTime) as LapTime From SectionTimes WHERE
SectionID=-1 --@LapSectionID
AND SectionTime > -1
AND RunID=3 --@RunID
Group By ResultItemID)
Order By ResultItemID
) t2 on t1.ResultItemID = t2.ResultItemID and t1.lap = t2.lap
WHERE RunID=@RunID and (ShortName Like 'S%' or SectionID=@LapSectionID)
Order By ResultItemID, sectionID

And it will return the following resutls:

0      L1              671484      105.77765069607      9
0      S1              27372      149.954166832729      9
0      S2              155411      81.2508299108347      9
0      S3              146354      91.217185727756      9
0      S4              87294      136.06058523235      9
0      S5              80258      108.995081770381      9
0      S6              92788      89.3532470891612      9
0      S7              26576      140.078539762465      9
0      S8              28491      144.782562914605      9
0      S9              26940      151.240973206452      9
1      L1              626497      113.373248395443      10
1      L1              626969      113.28789780675      9
1      L1              624907      113.661712862874      11
1      L1              627026      113.277599333999      12
1      L1              627491      113.193655367169      13
1      L1              630248      112.69849329153      14
1      S1              27759      147.863592151931      14
1      S1              27741      147.959534787695      13
1      S1              27660      148.392821928614      12
1      S1              27921      147.005675102806      11
1      S1              27788      147.709279348836      9
1      S1              28166      145.726956420701      10
1      S2              114933      109.866380650229      9
1      S2              115193      109.618403264719      10
1      S2              114572      110.212553916077      11
1      S2              115178      109.632679220621      12
1      S2              115273      109.542327581244      13
1      S2              114864      109.932378528283      14
1      S3              146767      90.9605020202089      14
1      S3              144694      92.263673683774      13
1      S3              143392      93.1014282526222      12
1      S3              144067      92.665218266501      11
1      S3              144329      92.4970033742353      10
1      S3              144173      92.5970882204019      9
1      S4              89322      132.971414962414      9
1      S4              89608      132.547012847879      10
1      S4              88591      134.068615629948      11
1      S4              89472      132.748488099883      12
1      S4              88810      133.738010666285      13
1      S4              88628      134.012645295761      14
1      S5              80536      108.618844649936      14
1      S5              79501      110.032921255422      13
1      S5              80161      109.126972876178      12
1      S5              79741      109.701750325771      11
1      S5              78817      110.987823346832      10
1      S5              79452      110.100781260727      9
1      S6              87634      94.6083608064118      9
1      S6              87337      94.9300879456484      10
1      S6              87387      94.8757720359904      11
1      S6              88506      93.6762376664756      12
1      S6              88811      93.3545291789203      13
1      S6              88285      93.910733317201      14
1      S7              26750      139.167374681393      14
1      S7              26567      140.125993628459      13
1      S7              26616      139.868021969014      12
1      S7              26561      140.157647405115      11
1      S7              26663      139.621470679491      10
1      S7              26602      139.941631182891      9
1      S8              28966      142.408340813367      9
1      S8              28575      144.356955380577      10
1      S8              28465      144.914807658528      11
1      S8              28519      144.640415161822      12
1      S8              28607      144.195476631594      13
1      S8              29129      141.611452504377      14
1      S9              27530      147.999702803553      14
1      S9              27487      148.23122996987      13
1      S9              27522      148.042722846516      12
1      S9              27602      147.613644597559      11
1      S9              27809      146.514862748816      10
1      S9              28099      145.002733840415      9
...

As you can see, it works great for ResultItemID 0, but when you get to ResultItemID 1, things go a little crazy.  I want to show the Maximum Lap in the result set for each ResultItemID.

If you were just to run the IN select for Lap qry (Select MAX(Lap) as MaxLap From SectionTimes WHERE
SectionID=-1
AND SectionTime > -1
AND RunID=3
Group By ResultItemID), you would get the following result set:

9
14
13
12
14
12
11
12
10
11
10
12
11
10

Any ideas how to associate the Maximum Lap with it's corresponding ResultItemID?

Thanks in advance!

Best Regards,
Eric

0
Comment
Question by:indy500fan
  • 3
4 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Instead of using IN in where clause, consider this:
Declare @LapSectionID int

Set @LapSectionID = -1

Declare @RunID int

Set @RunID=3
 

Select t1.ResultItemID, t1.ShortName, t1.Sectiontime, t1.SectionSpeed, t1.Lap

From SectionTimes t1 

inner join

(Select top 20 st.ResultItemID, st.Lap, st.SectionTime 

From SectionTimes st INNER JOIN (Select ResultItemID, Min(SectionTime) as LapTime From SectionTimes WHERE

SectionID=-1 --@LapSectionID

AND SectionTime > -1

AND RunID=3 --@RunID

Group By ResultItemID) AS ft

ON ft.ResultItemID = st.ResultItemID AND st.SectionTime = ft.LapTime

Where

st.SectionID=@LapSectionID

AND st.SectionTime > -1

AND st.RunID=@RunID

) t2 on t1.ResultItemID = t2.ResultItemID and t1.lap = t2.lap

WHERE RunID=@RunID and (ShortName Like 'S%' or SectionID=@LapSectionID)

Order By ResultItemID, sectionID

Open in new window

0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
I somehow lost one of the ORDER BY statements.  Basics of the solution is to move the IN statement from the WHERE clause and add to it the ResultItemID that you can use to join to main table.

 

Declare @LapSectionID int

Set @LapSectionID = -1

Declare @RunID int

Set @RunID=3
 

Select t1.ResultItemID, t1.ShortName, t1.Sectiontime, t1.SectionSpeed, t1.Lap

From SectionTimes t1 

inner join

(Select top 20 st.ResultItemID, st.Lap, st.SectionTime 

From SectionTimes st INNER JOIN (Select ResultItemID, Min(SectionTime) as LapTime From SectionTimes WHERE

SectionID=-1 --@LapSectionID

AND SectionTime > -1

AND RunID=3 --@RunID

Group By ResultItemID) AS ft

ON ft.ResultItemID = st.ResultItemID AND st.SectionTime = ft.LapTime

Where

st.SectionID=@LapSectionID

AND st.SectionTime > -1

AND st.RunID=@RunID

Order By st.ResultItemID

) t2 on t1.ResultItemID = t2.ResultItemID and t1.lap = t2.lap

WHERE RunID=@RunID and (ShortName Like 'S%' or SectionID=@LapSectionID)

Order By ResultItemID, sectionID

Open in new window

0
 

Author Closing Comment

by:indy500fan
Comment Utility
Brilliant!  Thank you!
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
>>Grading Comments: Brilliant! Thank you!

You are most welcome!

Happy coding...

/kev
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.

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

744 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

16 Experts available now in Live!

Get 1:1 Help Now