I am trying to design an SQL statement that will delete the MIN value(s) in one field.
I have three fields of interest:
Table: SUM
Fields: Parcel, Soil_Texture, Area
Ex. Parcel #1 is duplicated three times b/c there are three Soil Ttextures and three Areas associated with it:
i.e.
Parcel Soil_Texture Area
1 Loam 10
1 Silt 20
1 Clay 15
**Parcel=MALGISTAG, Soil_Texture=TEX_CSSC, Area=SumOfArea_CALC...I used simpler field names in my example.
I would like my result to delete two records and keep the Silt with Area=20. Keep in mind that there are thousands of Parcels with duplicate records.
Below is code that seems to be on the right path, but I get the error, "Specify the table containing the record you want to delete".
------------------------------------------------------------------------------------
DELETE *
FROM SUM
LEFT OUTER JOIN (SELECT MALGISTAG, MAX(SumOfAREA_CALC) AS MaxArea
FROM SUM
GROUP BY MALGISTAGl) AS MaxSubQuery ON SUM.MALGISTAG = MaxSubQuery.MALGISTAG AND SUM.SumOfAREA_CALC = MaxSubQuery.MaxArea
WHERE MaxSubQuery.MaxArea IS NULL