Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

Need help with a qry, Selecting IN a Where Clause

Friends,

I have the following qry:

Select ResultItemID, ShortName, Sectiontime, SectionSpeed
From SectionTimes
WHERE RunID=3
and ResultItemID=0
and Lap = 1
and (ShortName Like 'S%' or ShortName='L1')
Order By ResultItemID, sectionID

If I run this qry, I get the following Dataset:
ResultItemID  ShortName    SectionTime  SectionSpeed
0      L1              611643      116.126564025093
0      S1              27010      151.963919087207
0      S2              112215      112.527493893621
0      S3              140320      95.1396807297606
0      S4              87005      136.512530627811
0      S5              77232      113.26557997627
0      S6              86369      95.994038264992
0      S7              26184      142.175652028998
0      S8              28216      146.193648993479
0      S9              27092      150.392433861724

This is great, except that I have to fill in the Lap (Lap=1) and I have to tell it which ResultItemID (ResultItemID=0)

Now, I would like to get this data for all resultItemID's in this particular run (RunID=3), and base the lap on the resultItemID's Best Laptime.  

You can get the best Laptime for each resultItemID through the following qry:

Select MIN(SectionTime) as SectionTime, ResultItemID From SectionTImes WHERE SectionTIme > -1 and SectionID=-1 and RunID=3
Group By ResultItemID
Order By LapTime

The dataset for this qry would be:
SectionTime  ResultItemID
611643      0
614255      5
615515      13
616595      3
618441      2
621752      8
622991      10
624171      4
624907      1
626644      6
629242      11
634276      7
636396      9
638572      12

Any idea on how to combine these two so that I can get all the results I am looking for for all resultItemID's in the run?

Thanks in advance!

Best Regards,
Eric


0
indy500fan
Asked:
indy500fan
  • 5
  • 2
1 Solution
 
Daniel WilsonCommented:
Can you show us the desired results?  That makes it easier ... when we know what we're working toward!
0
 
indy500fanAuthor Commented:
I want the results that are shown in the first data set as above:
Like this:
0      L1              611643      116.126564025093
0      S1              27010      151.963919087207
0      S2              112215      112.527493893621
0      S3              140320      95.1396807297606
0      S4              87005      136.512530627811
0      S5              77232      113.26557997627
0      S6              86369      95.994038264992
0      S7              26184      142.175652028998
0      S8              28216      146.193648993479
0      S9              27092      150.392433861724

But I want them for every resultItemID so I would have:

0      L1              611643      116.126564025093
0      S1              27010      151.963919087207
0      S2              112215      112.527493893621
0      S3              140320      95.1396807297606
0      S4              87005      136.512530627811
0      S5              77232      113.26557997627
0      S6              86369      95.994038264992
0      S7              26184      142.175652028998
0      S8              28216      146.193648993479
0      S9              27092      150.392433861724
and...
5      L1              614255      115.632758382105
5      S1              26604      154.283019641612
5      S2              111395      113.355830398786
5      S3              140361      95.1118900549290
5      S4              87786      135.298028470060
5      S5              80209      109.061667303261
5      S6              86371      95.991815434684
5      S7              26477      140.602306633201
5      S8              28327      145.620785822713
5      S9              26725      152.457691980611      
and so on and so forth.  Is that what you mean?
0
 
Daniel WilsonCommented:
OK, just omit the ResultItemID from the WHERE clause:


Select ResultItemID, ShortName, Sectiontime, SectionSpeed
From SectionTimes
WHERE RunID=3
and Lap = 1
and (ShortName Like 'S%' or ShortName='L1')
Order By ResultItemID, sectionID

Open in new window

0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
indy500fanAuthor Commented:
Uh, not quite.  You see, it is a different lap for each resultitemID.  Most of the resultItemID's don't have the same bestlaptime on the same lap.  That is how I need to somehow tie the second qry in.

The second qry gives you the bestlaptime for each resultitemid, and somehow I need to tie that bestlaptime into the first qry so that I can determine which lap to use for each resultItemID.  This is where I think I need to use a IN clause.

Here is the schema for the table I am working with:

CREATE TABLE [dbo].[Sectiontimes](
      [RunID] [int] NOT NULL,
      [ResultItemID] [int] NOT NULL,
      [SectionID] [int] NOT NULL,
      [RowID] [int] NOT NULL,
      [LeaderLap] [int] NOT NULL,
      [Lap] [int] NOT NULL,
      [Flag] [int] NOT NULL,
      [EntryTime] [int] NOT NULL,
      [ExitTime] [int] NOT NULL,
      [SectionTime] [int] NOT NULL,
      [SectionSpeed] [float] NOT NULL,
      [AccumulatedTime] [int] NOT NULL,
      [DiffTime] [int] NOT NULL,
      [ShortName] [char](10) NOT NULL
) ON [PRIMARY]

And here would be a sample of data from all the columns...

3      5      -1      13      0      12      1      637247292      637861547      614255      115.63275838210517      16139823      -13372      L1        

Maybe that will help.
0
 
indy500fanAuthor Commented:
This is probably close to what I need, but it has errors:

Select ResultItemID, Lap From

(Select Min(SectionTime) as LapTime, ResultItemID, Lap From SectionTimes WHERE
SectionID=-1
AND SectionTime > -1
AND RunID=3
Group By ResultItemID, Lap) SubData

Where SubData.SectionTime IN (Select Min(SectionTime) as LapTime, ResultItemID From SectionTimes WHERE
SectionID=-1
AND SectionTime > -1
AND RunID=3
Group By ResultItemID)

I get the following errors:

Msg 207, Level 16, State 1, Line 9
Invalid column name 'SectionTime'.
Msg 116, Level 16, State 1, Line 9
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
0
 
indy500fanAuthor Commented:

Here is the solution I needed:

Select ResultItemID, Lap, SectionTime From
SectionTimes
Where
SectionID=-1
AND SectionTime > -1
AND RunID=3
AND
SectionTime IN (Select Min(SectionTime) as LapTime From SectionTimes WHERE
SectionID=-1
AND SectionTime > -1
AND RunID=3
Group By ResultItemID)

Best Regards,
Eric
0
 
indy500fanAuthor Commented:
Found a solution elsewhere.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now