Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-10-02
4
Medium Priority
?
234 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 2000 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

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…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

664 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