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.

 

 

-=-=-=-=-=-=-=-=-=-=-=-=-=-

Brian Piotrowski

Specialist - I.T.

Simcoe Parts Service, Inc.

Ph: 705-435-7814 x343

Fx: 705-435-6746

-=-=-=-=-=-=-=-=-=-=-=-=-=-