A better method: DROP VIEW Lcount CREATE VIEW Lcount AS SELECT COUNT(loc) AS lcount, loc FROM encountr GROUP BY loc SET VAR vlmaxcount INT = (MAX(lcount)) IN Lcount SET VAR vlmode INT = loc IN Lcount WHERE lcount = .vlmaxcount
-------------------------------------- At 01:30 PM 6/1/2002 -0700, you wrote: >The following will work to capture the Modal value. It ain't in >one statement but it does use views. Caveat: This works in >cases where there is a single modal value. Where there are two >or more values with the same frequency this process fails to >identify the true mode. > > >DROP VIEW Lcount >CREATE VIEW Lcount AS SELECT COUNT(loc) AS lcount, loc FROM encountr GROUP >BY loc >DROP VIEW Lmaxcount >CREATE VIEW Lmaxcount AS SELECT MAX(lcount) AS lmaxcount FROM Lcount >SELECT lmaxcount INTO vlmaxcount FROM Lmaxcount >SELECT loc INTO vlmode FROM Lcount WHERE lcount = .vlmaxcount > > >This took about 4 seconds to identify the mode on a nonindexed column >in a 15,000 row table on a Pentium III 700MHz Thinkpad. > > >At 12:47 PM 6/1/2002 -0700, you wrote: >>Anything that can go in a SELECT can be in a view. That includes >>MIN and MAX. I'm thinking about MODE. It might be that one will >>need two or three cascading views. >> >>At 11:40 AM 6/1/2002 -0700, you wrote: >>>I don't think a view will do it, since max, min and mode are not >>>functions of views. >>> >>>The way I would do this is to create a separate table that contains >>>3 currency cols such as minamt, maxamt, lastamt, and modamt, >>>and make a simple command file that executes after every >>>new entry, comparing the last amount to whatever is in >>>the minamt and maxamt and updating only if the lastamt is >>>greater than the maxamt, or less than the minamt. For the >>>mode, do a simple count for the last amount, and update it >>>only if its count is greater than the count for the existing >>>modamt. Of course if your table has values already, the >>>first time you'd have to do a tally, a comp max and a comp >>>min to see what the starting values are. >>> >>>bill >>> >>> >>>mjs wrote: >>> >>>> I have a table with >>>> 3 columns, Rdate_, service, and paid (date, text, and Currency). I want >>>> to >>>> have a view that will show me, for each service, the LAST price paid, >>>> the MAX paid, the LEAST paid, and if possible, the MOST COMMON price >>>> paid. (The services may be sold at different amounts on different >>>> dates.) I can easily make a new table and feed it with data generated >>>> by a declare >>>> cursor routine, but I would bet a SQL wizard could make a view that >>>> would do the same!! >>>> >>>> Mike Sinclair >>>> >>>> >>>> The rStreet List is dedicated to database application development, >>>> R:BASE, PHP, Visual Basic etc... >>>> >>>> All products mentioned on this list are the trademarks of their >>resepective companies. >>>> >>>> Post: [EMAIL PROTECTED] >>>> Unsubscribe: [EMAIL PROTECTED] >>>> http://groups.yahoo.com/group/rStreet/ >>>> >>>> Scott Salisbury - [EMAIL PROTECTED] >>>> >>>> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ >>> >>> >>> >>>The rStreet List is dedicated to database application development, >>>R:BASE, PHP, Visual Basic etc... >>> >>>All products mentioned on this list are the trademarks of their resepective >>companies. >>> >>>Post: [EMAIL PROTECTED] >>>Unsubscribe: [EMAIL PROTECTED] >>>http://groups.yahoo.com/group/rStreet/ >>> >>>Scott Salisbury - [EMAIL PROTECTED] >>> >>>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ >>> >>> >>> >> >> >> >>The rStreet List is dedicated to database application development, >>R:BASE, PHP, Visual Basic etc... >> >>All products mentioned on this list are the trademarks of their resepective >companies. >> >>Post: [EMAIL PROTECTED] >>Unsubscribe: [EMAIL PROTECTED] >>http://groups.yahoo.com/group/rStreet/ >> >>Scott Salisbury - [EMAIL PROTECTED] >> >>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ >> >> >> > > >------------------------ Yahoo! Groups Sponsor ---------------------~--> >Tied to your PC? Cut Loose and >Stay connected with Yahoo! Mobile >http://us.click.yahoo.com/QBCcSD/o1CEAA/sXBHAA/PhFolB/TM >---------------------------------------------------------------------~-> > >The rStreet List is dedicated to database application development, >R:BASE, PHP, Visual Basic etc... > >All products mentioned on this list are the trademarks of their resepective >companies. > >Post: [EMAIL PROTECTED] >Unsubscribe: [EMAIL PROTECTED] >http://groups.yahoo.com/group/rStreet/ > >Scott Salisbury - [EMAIL PROTECTED] > >Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ > > > >================================================ >TO SEE MESSAGE POSTING GUIDELINES: >Send a plain text email to [EMAIL PROTECTED] >In the message body, put just two words: INTRO rbase-l >================================================ >TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] >In the message body, put just two words: UNSUBSCRIBE rbase-l >================================================ >TO SEARCH ARCHIVES: >http://www.mail-archive.com/rbase-l%40sonetmail.com/ > ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
