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

Might be the field name DATE which I think is a reserved name.

The Order By is OK but using a field named DATE may not be....

Pete Helgren


Eric Graeb wrote:

I believe the problem is in the following line

ORDER BY SYMBOL,DATE DESC

You need a comma after date.
AS well, be careful with using orderby on fields that aren't in the select
statement as some SQL engines don't like this.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of James Newman, CDP
Sent: Friday, October 28, 2005 1:36 PM
To: MIDRANGE-L@xxxxxxxxxxxx
Subject: SQL avg, max, min of first 10 rows

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.
 Jim