At 04:52 PM 5/28/2014, Zeb Zbranek wrote:
What is the best way to locate the date of an event a maximum count occurs?
I can find the MAX(CaseCount), but not the associated date. The UNION worked
fine for the different date ranges I used to determine MAX(CaseCount), but
I need to know the actual date. I can find my MAX(CaseCount), but I cannot
pull up the associated date. If I have a table:GCCBY with two columns:
CaseYear(IYR4) and CaseCount(Integer); what search would I use to answer the
question, what year did I have the most cases?
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.
What I would like to see is:
R:>SELECT CaseYear, (MAX(CaseCount)) FROM GCCBY(This does not work), and get:
CaseYear | MAX(CaseCount)
2010 | 13
Any ideas?
Zeb,
First, welcome to R:BASE World!
It is sooooo nice to see an influx of new R:BASE eXtreme 9.5 (32/64) users
on this official R:BASE list.
Let's build it and they will come, with no distractions, is what I have
always believed and preached to my resilient R:BASE Dream Team and everyone
with whom I surround myself.
Having said that ...
You may want to look into the "TOP" option of the SELECT command to achieve
your goal.
For example ...
CONNECT RRBYW18
SELECT TOP 5 EmpID,Bonus FROM SalesBonus WHERE Bonus < 500 ORDER BY Bonus=DESC
Once you understand the concept, you should be able to customize the syntax.
Hope that helps!
Feel free to reach out with further questions.
Very Best R:egards,
Razzak.
www.rbase.com
www.facebook.com/rbase
--
31 years of continuous innovation!
16 Years of R:BASE Technologies, Inc. making R:BASE what it is today!
--
