× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

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

 


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.