Solved

Need help with a qry, Selecting IN a Where Clause

Posted on 2008-10-01
7
242 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

792 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