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/
