Mike 1. The last price
SELECT Service,RDate,Price FROM Table WHERE Service = 'the service you want' AND RDate = (SELECT MAX(RDate) FROM table WHERE Service = 'the service you want') 2. The top price SELECT Service,RDate,Price FROM Table WHERE Service = 'the service you want' AND Price = (SELECT MAX(Price) FROM table WHERE Service = 'the service you want') 3. The least price SELECT Service,RDate,Price FROM Table WHERE Service = 'the service you want' AND Price = (SELECT MIN(Price) FROM table WHERE Service = 'the service you want') 4. The most common price If by this you mean the price with the most number of rows in the table, then 1st you need to define a view that shows number of records for each service / price combination: CREATE VIEW NumPrice (Service,Price,Number) AS SELECT Service,Price,COUNT(*) FROM Table GROUP BY Service,Price Now to find which price is most common for a given service: SELECT Service,Price,Number FROM NumPrice WHERE Service = 'the service you want' AND Number = (SELECT MAX(Number) FROM NumPrice WHERE Service = 'the service you want' David Blocker ----- Original Message ----- From: "mjs" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; "Rbase List Server" <[EMAIL PROTECTED]> Sent: Sunday, June 02, 2002 1:28 PM Subject: Re: [rStreet] How do you view? > Ok, perhaps you could show me how to do this with a single example. A salesman is trying > to see the prices that other salesman have sold a particular service for. The table with > the raw data has 3 columns, rdate_ , service, and price. In the raw data table, there are > about 100,000 rows, 100 different services, and dates starting from 2 years ago. > So.......how does the saleman quickly figure out the last price, the max price, the least > price, and the most common price that the chosen service has sold for? Can that data be > shown in a view some how? > > Mike Sinclair > > Bernie Corrigan wrote: > > > You handle that with the WHERE and GROUP BY clauses in the view definitions. > > What do you > > mean "recreate the data"? You are looking for statistics on welldefined > > subsets of your > > data. Those subsets can be specified with WHERE and/or GROUP BY clauses. I > > think the > > process you describe can be done without using a cursor. There are really a > > lot of > > different ways you can go here. > > > > At 05:59 PM 6/1/2002 -0400, you wrote: > > >What I have is a table with about 100,000 rows of data. What I did was use > > a program at > > >the end of the day to calculate the new min, max, last, and common for each > > service, but I > > >used a declare cursor routine to do it. The only advantage, is that the > > data is instantly > > >available, but it does take some time at the end of the day to run that > > file. From what > > >I've been reading, there might be a to create a view that would just > > recreate the data on > > >the fly as needed for any given service, but maybe that's too much to ask for. > > > > > >Mike Sinclair > > > > > >Bernie Corrigan wrote: > > > > > >> Since we are dealing with R:Base I wrote my second example in R:Base. My > > >> first example was more like what you have below. The ANSI SQL SELECT > > >> statement puts the query results into a temporary results table and it is up > > >> to the programming language to furnish a way to get what you want out of > > >> that table. R:Base provides some fairly easy to use methods. Other > > >> languages also have the INTO clause. This includes DB2 and Oracle among > > >> others although it can mean different things in different languages. > > >> > > >> At 02:03 PM 6/1/2002 -0700, you wrote: > > >> >Bernie Corrigan wrote: > > >> > > > >> >> 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 > > >> > > > >> >SELECT MAX(lCount) + > > >> > INTO vlMaxCount + > > >> > FROM lCount > > >> > > > >> >> SET VAR vlmode INT = loc IN Lcount WHERE lcount = .vlmaxcount > > >> > > > >> >SELECT Loc + > > >> > INTO vlMode + > > >> > FROM lCount + > > >> > WHERE lCount = .vlMaxCount > > >> > > > >> >Bernie, your syntax isn't SQL. The > > >> >syntax I've got is at least partially > > >> >SQL. There is no "INTO VarName" > > >> >clause in SQL but that "IN" stuff is > > >> >really old fashioned. That stuff should > > >> >have gone out when you started in > > >> >2.11. I know you're going to disagree > > >> >with me but... ...it ain't SQL. > > >> > > > >> >Scott > > >> >==== > > >> > > > >> > > > >> > > > >> > > > >> >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/
