Solved

Need help with a qry, Selecting IN a Where Clause

Posted on 2008-10-01
7
241 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

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…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

816 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now