Re: SQL avg, max, min of first 10 rows

James,

When using agregate functions you need a GROUP BY clause before the ORDER BY. 

SELECT SYMBOL, DATE,MAX(CLOSE), MIN(CLOSE)
FROM TESTTABLE
WHERE SYMBOL = 'DELL ' AND DATE = '2005-03-18'
GROUP BY SYMBOL, DATE
ORDER BY SYMBOL, DATE DESC
FETCH FIRST 10 ROWS ONLY

In order to use ORDER BY the fields need to be in the resultset (in the SELECT).

Regards,
Carel Teijgeler

*********** REPLY SEPARATOR  ***********

On 28-10-05 at 13:36 James Newman, CDP wrote:

>FWIW, I'm on a 600 running v5r2. I'm still pretty new to SQL; please, be 
>gentle.
> I'm trying to find the max and min of 10 records once I've positioned to
>program to a certain date in the table. I've been trying this using STRSQL
>before putting code into a program. Using the following code I can get the
>correct 10 records to display:
> SELECT SYMBOL,DATE,CLOSE
>FROM TESTTABLE
>WHERE SYMBOL='DELL ' AND DATE = '2005-03-18'
>ORDER BY SYMBOL,DATE DESC
>FETCH FIRST 10 ROWS ONLY
> Then I tried this to get maximum and minimum:
> SELECT MAX(CLOSE), MIN(CLOSE)
>FROM TESTTABLE
>WHERE SYMBOL = 'DELL ' AND DATE = '2005-03-18'
>ORDER BY SYMBOL, DATE DESC
>FETCH FIRST 10 ROWS ONLY
> I get a message saying "Column DATE or expression in select list is not
>valid". Do I need to do something like run the first SQL statement to get
>those records then run another SQL statement against those records, like a
>nested statement? TIA.