Solved

Need help with a qry, Selecting IN a Where Clause

Posted on 2008-10-01
7
243 Views
Last Modified: 2010-03-19
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
Comment
Question by:indy500fan
  • 5
  • 2
7 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22617883
Can you show us the desired results?  That makes it easier ... when we know what we're working toward!
0
 

Author Comment

by:indy500fan
ID: 22617941
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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22617988
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
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!

 

Author Comment

by:indy500fan
ID: 22618142
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
 

Author Comment

by:indy500fan
ID: 22618656
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
 

Author Comment

by:indy500fan
ID: 22624037

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
 

Accepted Solution

by:
indy500fan earned 0 total points
ID: 22624059
Found a solution elsewhere.
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

713 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