Learn how to a build a cloud-first strategyRegister Now

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

Complex Query Help needed

Hi I have a table that holds fish that have been checked in at a youth fishing derby.

I need to write a query that will give me the top (n) kids by way of the length of the fish.

We will be checking in all species of fish however only serveral will be taken into consideration for the prizes.

I have written this query that asks for a resultset containing all of the fish checked in for the speicies of fish that will be awared prizes:

SELECT     FishID, Length, CheckInTime, SpeciesID, KidID
FROM         tblFish
WHERE     (SpeciesID IN (1, 2)) -- 1 is Rainbow Trout, 2 is Brook Trout

This query returns the following resultset


FishID      Length       CheckInTime                         SpeciesID   KidID      
----------- ------------    ---------------------------            ----------- -----------
1             11.0625      2005-04-20 20:51:29.657     1           1
3             13.5000      2005-04-21 08:47:19.780     2           1
4             17.0000      2005-05-07 18:44:34.390     1           1
5             12.5000      2005-05-07 18:45:54.263     1           1
6             12.5000      2005-05-07 18:46:33.983     1           1
10           12.0000      2005-05-07 18:59:48.107     2           3
12           12.2500      2005-05-07 21:49:28.093     1           4
18           12.0000      2005-05-08 02:02:57.937     2           9
38           12.7500      2005-05-08 13:23:24.623     1           3
39           17.0000      2005-05-08 13:23:31.780     2           3
40           12.7500      2005-05-08 13:33:51.390     2           3
41           12.7500      2005-05-08 13:34:17.047     2           2
45           12.2500      2005-05-08 13:38:23.717     2           2
46           14.2500      2005-05-08 13:39:41.920     2           5
47           12.7500      2005-05-08 13:39:48.593     1           5
48           17.0000      2005-05-08 13:39:54.843     2           5
49           15.0000      2005-05-08 13:42:10.653     1           6
50           13.0000      2005-05-08 13:42:17.297     2           6
51           22.5000      2005-05-08 13:42:27.857     2           6
52           21.7500      2005-05-08 13:42:48.390     2           7
53           22.5000      2005-05-08 13:42:56.373     2           7
54           13.7500      2005-05-08 13:43:06.967     2           7
55           22.5000      2005-05-08 13:43:13.607     1           7
56           18.0000      2005-05-08 13:44:20.933     2           8
57           18.5000      2005-05-08 13:44:34.763     1           8
58           13.6250      2005-05-08 13:44:49.077     2           8
59           13.3750      2005-05-08 13:45:17.250     1           9
60           16.8750      2005-05-08 13:45:24.373     2           9
61           19.0000      2005-05-08 13:45:38.200     1           9
62           12.8750      2005-05-08 13:46:48.200     2           10
63           13.5000      2005-05-08 13:46:55.327     1           10
64           14.0000      2005-05-08 13:47:43.280     1           11
65           12.5000      2005-05-08 13:47:50.920     2           11
66           14.2500      2005-05-08 13:47:59.107     1           11
67           12.0625      2005-05-08 13:48:06.640     2           11
68           10.5000      2005-05-08 13:48:49.047     2           13
69           12.1250      2005-05-08 13:48:56.670     2           13
70           19.5000      2005-05-08 13:49:03.310     1           13
71           12.5000      2005-05-08 13:51:56.560     1           14
72           19.5000      2005-05-08 13:52:02.153     1           14
73           12.7500      2005-05-08 13:52:08.560     2           14
74           14.3750      2005-05-08 13:52:17.327     2           14

I must re-write this query so that I get the follwing results:

The top 10 biggest fish caught ordered by the largest to the smallest.
A childID must only appear once in the list with their biggest fish(don't want a child taking 1st place AND 3rdPlace)
If 2 children catch the same size fish the one who checked in the earliest must appear one place higher than the other

I will also need to join in the Kid table and the species table to get the Child's Name (from the KidID) and the Species Name (from the SpeciesID)

Thanks for any help.

I would aware more points but it's all I have left in the bank.


0
Keyman
Asked:
Keyman
  • 15
  • 12
  • 2
1 Solution
 
cyberdevil67Commented:
Hi Keyman,

SELECT     FishID, top 10 Length, CheckInTime, SpeciesID, KidID
FROM         tblFish
WHERE     (SpeciesID IN (1, 2)) -- 1 is Rainbow Trout, 2 is Brook Trout
order by  Length Desc

Cheers!
0
 
Anthony PerkinsCommented:
You may find experts more responsive if you take the time to maintain your abandoned questions. Here are listed all your open questions:
1 09/01/2004 500 Installtion of Exchange 2003  Open Exchange Server
2 09/17/2004 50 Message Lost in Que  Open Exchange Server
3 03/20/2005 250 Cisco 2514 > Ethereal : I am not seeing ...  Open Routers/Switches
4 04/19/2005 125 MS Access Setting Focus and Value of a t...  Open Microsoft Access
5 05/08/2005 240 Complex Query Help needed  Open Microsoft SQL Server
6 07/14/2004 100 Passing Value to Query From Multiple For...  Open Microsoft Access

Thanks.


cyberdevil67,
Your result (after correcting the TOP 10 sysntax error):
FishID      Length         CheckInTime                                            SpeciesID   KidID      
----------- -------------- ------------------------------------------------------ ----------- -----------
51          22.5000        2005-05-08 13:42:27.857                                2           6
53          22.5000        2005-05-08 13:42:56.373                                2           7
55          22.5000        2005-05-08 13:43:13.607                                1           7
52          21.7500        2005-05-08 13:42:48.390                                2           7
70          19.5000        2005-05-08 13:49:03.310                                1           13
72          19.5000        2005-05-08 13:52:02.153                                1           14
61          19.0000        2005-05-08 13:45:38.200                                1           9
57          18.5000        2005-05-08 13:44:34.763                                1           8
56          18.0000        2005-05-08 13:44:20.933                                2           8
39          17.0000        2005-05-08 13:23:31.780                                2           3
0
 
Anthony PerkinsCommented:
Actually what makes this problem interesting is that KidID = 7 has both species with coincidentally the same length, which makes me think that perhaps this is a homework assignment.  

In any case, once you get past that the query is not too difficult.  This is the result I get:

FishID      Length         CheckInTime                                            SpeciesID   KidID      
----------- -------------- ------------------------------------------------------ ----------- -----------
51          22.5000        2005-05-08 13:42:27.857                                2           6
53          22.5000        2005-05-08 13:42:56.373                                2           7
70          19.5000        2005-05-08 13:49:03.310                                1           13
72          19.5000        2005-05-08 13:52:02.153                                1           14
61          19.0000        2005-05-08 13:45:38.200                                1           9
57          18.5000        2005-05-08 13:44:34.763                                1           8
4           17.0000        2005-05-07 18:44:34.390                                1           1
39          17.0000        2005-05-08 13:23:31.780                                2           3
48          17.0000        2005-05-08 13:39:54.843                                2           5
66          14.2500        2005-05-08 13:47:59.107                                1           11
0
Independent Software Vendors: 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!

 
KeymanAuthor Commented:
What is the query you used to get that result?
0
 
cyberdevil67Commented:
SELECT     FishID, top 10 Length, CheckInTime, SpeciesID, Distinct KidID
FROM         tblFish
WHERE     SpeciesID IN (1, 2)
order by  Length Desc
0
 
KeymanAuthor Commented:
Well that gives me a syntax error
0
 
Anthony PerkinsCommented:
>>Well that gives me a syntax error<<
I know I already pointed that out.
0
 
KeymanAuthor Commented:

AcPerkins,

What did you do to the query to get it almost where I need it?
0
 
Anthony PerkinsCommented:
>>What did you do to the query to get it almost where I need it?<<
What do you mean "almost"?  Are the results not entirely correct?  Is so what should they be?
0
 
Anthony PerkinsCommented:
P.S. Thanks for maintaining your open questions.  In future, you should post requests for refunds in Community Support and not Cleanup.
0
 
KeymanAuthor Commented:
"perhaps this is a homework assignment"

I thought that meant you discovered a possible anomaly in the resultset.

In any event what did you change, what is the query you used?
0
 
Anthony PerkinsCommented:
By the way, this is not a homework assignment is it?
0
 
KeymanAuthor Commented:
Sorry I thought thats what "clean up" was for... I did not take the time to read exactly what forum was for what requests.

0
 
Anthony PerkinsCommented:
Are questions crossed.  It was more like the question was just too pat to be real.

Are the results correct?
0
 
Anthony PerkinsCommented:
Here is the query:

Select      TOP 10
      f.FishID,
      f.Length,
      f.CheckInTime,
      f.SpeciesID,
      f.KidID
From      tblFish f
      Inner Join (
            Select      d2.Length,
                  MIN(d2.CheckInTime) MinCheckInTime,
                  d2.KidID
            From      tblFish d2
                  Inner Join (
                        Select      KidID,
                              MAX(Length) MaxLength
                        From      tblFish
                        Where      SpeciesID IN (1, 2)
                        Group BY
                              KidID) d1 On d2.KidID = d1.KidID And d2.Length = d1.MaxLength
            Group By d2.Length, d2.KidID) d3 On f.KidID = d3.KidID And f.Length = d3.Length And f.CheckInTime = d3.MinCheckInTime
Order By
       f.Length Desc, f.CheckInTime

Enjoy.
0
 
KeymanAuthor Commented:
Oh this is not a homework assignment.

I am co-chairman of an annual fishing derby.

We had some problems with the way we handled the fish check in this past year and I want to go computerized for next year.


http://www.eljaycees.org/2005FishingDerby/2005derby.html <-- Info

http://jaycees.no-ip.org/ Last years photos
0
 
KeymanAuthor Commented:
What do you mean by too "pat" ?

0
 
Anthony PerkinsCommented:
>>What do you mean by too "pat" ?<<
It is an adjective and a synonym for contrived.

But I see now that I was wrong and I stand corrected.
0
 
KeymanAuthor Commented:
Thanks for the help.
0
 
Anthony PerkinsCommented:
P.S. In case you did not know a Brook Trout, does not belong to the Trout species it is actually a char.  So why do we always call it Brook Trout?

But I diverge.
0
 
Anthony PerkinsCommented:
Glad I could help, and once again thanks for updating your open questions.
0
 
KeymanAuthor Commented:
Yes this is true for the Lake Trout as well.
0
 
Anthony PerkinsCommented:
I seemed to have overlooked this part of the question:
>>I will also need to join in the Kid table and the species table to get the Child's Name (from the KidID) and the Species Name (from the SpeciesID)<<

If you still need it, post the structure of the Kid and Species tables.
0
 
KeymanAuthor Commented:
CREATE TABLE [tblKid] (
      [KidID] [int] NOT NULL ,
      [FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Age] [int] NOT NULL ,
      [Gender] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [PoleRental] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [PoleID] [int] NULL ,
      [Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [State] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ZipCode] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      CONSTRAINT [PK_tblKid] PRIMARY KEY  CLUSTERED
      (
            [KidID]
      )  ON [PRIMARY]
) ON [PRIMARY]
GO

------------------------------------------------

CREATE TABLE [tblSpecies] (
      [SpeciesID] [int] IDENTITY (1, 1) NOT NULL ,
      [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      CONSTRAINT [PK_tblSpecies] PRIMARY KEY  CLUSTERED
      (
            [SpeciesID]
      )  ON [PRIMARY]
) ON [PRIMARY]
GO




0
 
Anthony PerkinsCommented:
>>Yes this is true for the Lake Trout as well.<<
So that we do not run afoul of the EE police with any more off-line comments, if you have a link that describes this I would be interested in reading it.  I used to fly fish for trout as a kid in Argentina.
0
 
Anthony PerkinsCommented:
See if this works for you (untested):

Select      TOP 10
      f.FishID,
      f.Length,
      f.CheckInTime,
      s.[Name] SpeciesName,
      k.FirstName,
      k.LastName
From      tblFish f
      Inner Join (
            Select      d2.Length,
                  MIN(d2.CheckInTime) MinCheckInTime,
                  d2.KidID
            From      tblFish d2
                  Inner Join (
                        Select      KidID,
                              MAX(Length) MaxLength
                        From      tblFish
                        Where      SpeciesID IN (1, 2)
                        Group BY
                              KidID) d1 On d2.KidID = d1.KidID And d2.Length = d1.MaxLength
            Group By d2.Length, d2.KidID) d3 On f.KidID = d3.KidID And f.Length = d3.Length And f.CheckInTime = d3.MinCheckInTime
      Inner Join tblSpecies s On f.SpeciesID = s.SpeciesID
      Inner Join tblKid k On f.KidID = k.KidID
Order By
       f.Length Desc, f.CheckInTime
0
 
KeymanAuthor Commented:
Lots of sites out there saying it belongs to the Char family but I found one that has a little more credibility than the others (coming from Cornell and all!):

http://fish.dnr.cornell.edu/nyfish/Salmonidae/laketrout.html
0
 
KeymanAuthor Commented:
Thanks that worked well.
0
 
Anthony PerkinsCommented:
Thanks, excellent article.  All those years I thought I was fishing trout <sigh>
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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