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

Jim,

 
Select max(close), min(close)
>From ( SELECT SYMBOL,DATE,CLOSE
       FROM TESTTABLE
       WHERE SYMBOL='DELL ' AND DATE = '2005-03-18'
       ORDER BY SYMBOL,DATE DESC
       FETCH FIRST 10 ROWS ONLY
      ) as tbl


I perfer this format:

with tbl as ( SELECT SYMBOL,DATE,CLOSE
              FROM TESTTABLE
              WHERE SYMBOL='DELL ' AND DATE = '2005-03-18'
              ORDER BY SYMBOL,DATE DESC
              FETCH FIRST 10 ROWS ONLY
             )
Select max(close), min(close)
>From tbl

HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of James Newman, CDP
Sent: Friday, October 28, 2005 2: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