Re: SQL Ideas Please

Guess I don't understand 1.  Sample data please.

2 - If there are not any other fields you are interested in wouldn't the 
following work:
Sample table
....+....1....+....2....+....3....+....4....+...
SCACODE     TRLRNUM   ASNNUM  ORDERTYP  ORDERNUM
ABCD          1,000   39210      2        52,971
ABCD          1,000   39210      2        52,951
ABCD          1,000   39210      2        52,991
ABCD          1,000   39210      2        53,001
********  End of data  ******** 
Sample sql statement
select SCACODE, TRLRNUM,ASNNUM, ORDERTYP, min(ORDERNUM)
from qtemp/brian 
group by SCACODE, TRLRNUM,ASNNUM, ORDERTYP 
....+....1....+....2....+....3....+....4....+....5....+.
SCACODE     TRLRNUM   ASNNUM  ORDERTYP  MIN ( ORDERNUM )
ABCD          1,000   39210      2            52,951 
********  End of data  ******** 
If there ARE other fields I suppose you could then do
ALTER TABLE QTEMP/BRIAN ADD COLUMN NEWFIELD CHAR (5 ) NOT NULL WITH
DEFAULT 
... (series of updates omitted) ...
select * from qtemp/brian
....+....1....+....2....+....3....+....4....+....5....+....
SCACODE     TRLRNUM   ASNNUM  ORDERTYP  ORDERNUM   NEWFIELD
ABCD          1,000   39210      2        52,971    A 
ABCD          1,000   39210      2        52,951    B 
ABCD          1,000   39210      2        52,991    C 
ABCD          1,000   39210      2        53,001    D 
********  End of data  ******** 

with t1 as( 
select SCACODE, TRLRNUM,ASNNUM, ORDERTYP, min(ORDERNUM) as minnbr
from qtemp/brian 
group by SCACODE, TRLRNUM,ASNNUM, ORDERTYP) 
select SCACODE, TRLRNUM,ASNNUM, ORDERTYP, ORDERNUM, newfield 
from qtemp/brian 
where (SCACODE, TRLRNUM,ASNNUM, ORDERTYP, ORDERNUM) in( 
select SCACODE, TRLRNUM,ASNNUM, ORDERTYP, minnbr 
from t1) 
....+....1....+....2....+....3....+....4....+....5....+....
SCACODE     TRLRNUM   ASNNUM  ORDERTYP  ORDERNUM   NEWFIELD
ABCD          1,000   39210      2        52,951    B 
********  End of data  ******** 
Works fine on my V5R4 machine.  Prior to V5R4 you have to do the 
concatenation others have mentioned.  So the above becomes
with t1 as( 
select SCACODE, TRLRNUM,ASNNUM, ORDERTYP, min(ORDERNUM) as minnbr
from qtemp/brian 
group by SCACODE, TRLRNUM,ASNNUM, ORDERTYP) 
select SCACODE, TRLRNUM,ASNNUM, ORDERTYP, ORDERNUM, newfield 
from qtemp/brian 
where SCACODE concat char(trlrnum) concat asnnum concat ordertyp 
  concat char(ordernum) in( 
select SCACODE concat char(TRLRNUM) concat ASNNUM 
  concat ordertyp concat char(minnbr) 
from t1) 


Rob Berendt