I haven't read all the responses yet, so, if someone else has already given
you this or a better solution, superb. Anyway, start w/these and play w/'em
some to see if it helps.
SELECT +
Service, +
MAX(AmountPaid), +
MIN(AmountPaid), +
AVG(AmountPaid) + -- Not same as MODE, but you get the picture
FROM +
MIKES_SERVICES +
GROUP BY +
Service
-- *** F/finding the MODE
SELECT +
Service, +
AmountPaid, +
MAX(COUNT(*)), + -- You know, this might not work, but a variant might
COUNT(*)
FROM +
MIKES_SERVICES +
GROUP BY +
Service, +
AmountPaid
-- *** I think this'll show you the most recent price by service
SELECT +
Service, +
MAX(AmountPaid) +
FROM +
MIKES_SERVICES +
WHERE +
ServiceDT = (SELECT MAX(ServiceDT) FROM MIKES_SERVICES ms +
WHERE ms.Service = MIKES_SERVICES.Service) +
GROUP BY +
Service
You can look in the legacy doc's in the help files w/RB and find some decent
treatments of this topic.
Look at :
C:\RBTI\RBWIN65PP\RBWin65\TechDocs\824.HTM
C:\RBTI\RBWIN65PP\RBWin65\TechDocs\825.HTM
C:\RBTI\RBWIN65PP\RBWin65\TechDocs\834.HTM
C:\RBTI\RBWIN65PP\RBWin65\TechDocs\841.HTM
Joe Celko also treats this in "SQL For Smarties".
HTH,
Steve in Memphis
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
> Behalf Of mjs
> Sent: Saturday, June 01, 2002 12:18 PM
> To: Rbase List Server; rStreet yahoo
> Subject: How do you view?
>
>
> 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
>
>
>
> ================================================
> 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/
