RE: SQL Ideas Please

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Brian, I'm not sure I fully understood you (especially DISTINCT part - SQL
keyword?) so make sure you test this on a dummy/copied file first.  
Alternatively, just run the SELECT part on its own to verify that projected
rows are ones you want to preserve.

WITH preserve AS (
  SELECT SCACCODE,TRLRNUM,ASNNUM,ORDERTYP,ORDERNUM,MIN(FDATE) AS FDATE 
  FROM SSW85
  GROUP BY SCACCODE, TRLRNUM, ASNNUM, ORDERTYP, ORDERNUM)
DELETE FROM SSW85 A, preserve P
WHERE A.SCACODE  = P.SCACODE  AND 
      A.TRLRNUM  = P.TRLRNUM  AND 
      A.ASNNUM   = P.ASNNUM   AND
      A.ORDERTYP = P.ORDERTYP AND
      A.ORDERNUM = P.ORDERNUM AND 
      A.FDATE   <> P.FDATE

Elvis

-----Original Message-----
Subject: SQL Ideas Please

Hi All,

(This is a two-part SQL question)

Can someone tell me if there is an easy way to delete records from a
table where there are only four data elements on which I wish to base
the delete routine?  For example, I have a table that has numerous
records in it.  I want to delete all records other than those that have
a distinct SCACCODE, TRLRNUM, ASNNUM, ORDERTYP and ORDERNUM.  However,
I'm not sure how to express this in SQL, but in pseudo-code, I'd like to
do this:

DELETE ALL RECORDS IN SSW85 EXECPT THOSE RECORDS THAT HAVE A DISTINCT
(SCACCODE, TRLRNUM, ASNNUM, ORDERTYP, ORDERNUM).

My second SQL statement needs to pass over the records again and only
keep records that have the minimum number for a specific column.  For
example, if I have these records:

A          B          C                      D          E          F

ABCD   1000     10039210          2          105049  206052971
ABCD   1000     10039210          2          105049  206052951
ABCD   1000     10039210          2          105049  206052991
ABCD   1000     10039210          2          105049  206053001

I only want to keep one of these records that has the minimum value in
column F.  So if I ran a min(COLUMN_F) on this, I would only get back
the second record (since it is the lowest number for Column F).
Pseudo-code wise, I'd need something like:

DELETE ALL RECORDS IN SSW85 EXCEPT EACH DISTINCT (SCACCODE, TRLRNUM,
ASNNUM, ORDERTYP, ORDERNUM) WITH THE LOWEST KDLOTSTR.

Is there an easy way to do both of these statements (or combine them
into one statement)?

Thanks!

Brian.