Keyman
asked on
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.
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.
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
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
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
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
ASKER
What is the query you used to get that result?
SELECT FishID, top 10 Length, CheckInTime, SpeciesID, Distinct KidID
FROM tblFish
WHERE SpeciesID IN (1, 2)
order by Length Desc
FROM tblFish
WHERE SpeciesID IN (1, 2)
order by Length Desc
ASKER
Well that gives me a syntax error
>>Well that gives me a syntax error<<
I know I already pointed that out.
I know I already pointed that out.
ASKER
AcPerkins,
What did you do to the query to get it almost where I need it?
>>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?
What do you mean "almost"? Are the results not entirely correct? Is so what should they be?
P.S. Thanks for maintaining your open questions. In future, you should post requests for refunds in Community Support and not Cleanup.
ASKER
"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?
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?
By the way, this is not a homework assignment is it?
ASKER
Sorry I thought thats what "clean up" was for... I did not take the time to read exactly what forum was for what requests.
Are questions crossed. It was more like the question was just too pat to be real.
Are the results correct?
Are the results correct?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
What do you mean by too "pat" ?
>>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.
It is an adjective and a synonym for contrived.
But I see now that I was wrong and I stand corrected.
ASKER
Thanks for the help.
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.
But I diverge.
Glad I could help, and once again thanks for updating your open questions.
ASKER
Yes this is true for the Lake Trout as well.
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.
>>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.
ASKER
CREATE TABLE [tblKid] (
[KidID] [int] NOT NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[Age] [int] NOT NULL ,
[Gender] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[PoleRental] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[PoleID] [int] NULL ,
[Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[State] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[ZipCode] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_
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_
CONSTRAINT [PK_tblSpecies] PRIMARY KEY CLUSTERED
(
[SpeciesID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
[KidID] [int] NOT NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[Age] [int] NOT NULL ,
[Gender] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[PoleRental] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[PoleID] [int] NULL ,
[Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[State] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[ZipCode] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_
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_
CONSTRAINT [PK_tblSpecies] PRIMARY KEY CLUSTERED
(
[SpeciesID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
>>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.
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.
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
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
ASKER
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
http://fish.dnr.cornell.edu/nyfish/Salmonidae/laketrout.html
ASKER
Thanks that worked well.
Thanks, excellent article. All those years I thought I was fishing trout <sigh>
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!