Zeb, I probably should have simplified this; I just took a previously existing
query and tested it, then pasted it, but, related to what I think your need is,
the following might have been better:
SELECT SeasonCCYY,
COUNT(NameOfAthlete)
FROM INDIVIDUAL_RESULTS
WHERE ETInSeconds <> 0
GROUP BY
SeasonCCYY
ORDER BY
2 DESC
Translation:
Get the year,
total number of runners
from individual performance records
where the runner crossed the finish line
grouped by
the year
ordered by
how many finishers during the season from most to least
From: [email protected] [mailto:[email protected]] On Behalf Of Wills, Steve
Sent: May 28, 2014 4:38 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: Date Where Max(CaseCount) Occurs
Zeb (and Bill), what about something like this:
SELECT SeasonCCYY,
NameOfSchoolCD,
COUNT(NameOfAthlete)
FROM INDIVIDUAL_RESULTS
WHERE ETInSeconds <> 0
AND NameOfSchoolCD IN ('SAA','SDS')
GROUP BY
SeasonCCYY,
NameOfSchoolCD,
ORDER BY
2,
3 DESC
Translation:
Get the season,
name of the school
how many runner names
from individual results table
where performance/elapsed time has a positive integer value
and name of the school is for my girls and my boys teams
group by
the season,
name of the school
order by
name of the school (to keep boys and girls counts
together)
how many runner names from most to least (IOW, which year’s team has had the
most runners)
From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On
Behalf Of Bill Downall
Sent: May 28, 2014 4:23 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: Date Where Max(CaseCount) Occurs
On Wed, May 28, 2014 at 5:18 PM, A. Razzak Memon
<[email protected]<mailto:[email protected]>> wrote:
It currently takes me two steps to answer this question: (1)
SELECT MAX(CaseCount) FROM GroupCaseCountByYear; (2) SELECT CaseYear WHERE
CaseCount = #. (#--is the number I got from step (1)) This seems counter
productive.
Zeb,
You can combine your two queries into one:
SELECT CaseYear FROM GroupCaseCountByYear +
WHERE CaseCount = (SELECT MAX (CaseCount) FROM GroupCaseCountByYear)
