Mike, The min, max, and count are easy.
create vie myview (service, max_price, min_price, service_count as + select service, (max(price)), (min(price)), (count (*)) from mytable group by service To get the first and the last price, it gets tricky because you have to get the appropriate row with the last or first date before you can get the price. I would have to think about it a while to get the complete view and you may need to do a structure change to get it. But if you just wanted for the first price from one service '123', you could do this: select service, rdate, price from mytable t1 where t1.service = '123' and t1.rdate in (select (min(t2.rdate)) from mytable t2 where t1.service = t2.service) Now to modify this so you get all the services in one view, I think you would need to add a computed column to your table or build another view so that you get a unique key for the service & date combination. Troy Sosamon ===== Original Message from [EMAIL PROTECTED] at 6/04/02 7:10 am >Can somebody give me an example of the syntax to create a single table >view using group by and having? If I have a table with 3 coulums, rdate >service and price, and I want the view to show just 1 row for each >service and some permutation of the price (such as min, max, last, >first, count, etc), what magic sentence would make that view? > >TIA > >Mike Sinclair > >================================================ >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/
