Solved

Need help with a qry, Selecting IN a Where Clause

Posted on 2008-10-01
7
246 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
[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
  • 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
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.

 

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

622 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