That's how I'm doing it now, but I keep hearing that I should try to get away from DECLARE CURSOR stuff and use views. But I don't know how to create a view like that!
Mike Sinclair William Stacy 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/ > > ------------------------ 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/
