Solved

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

Posted on 2008-10-02
4
229 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
[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
4 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22625862
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 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 22625873
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
ID: 31502442
Brilliant!  Thank you!
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22626618
>>Grading Comments: Brilliant! Thank you!

You are most welcome!

Happy coding...

/kev
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

734 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