Querying out the largest value? 3

[Hunguyen]

To whomever can assist:

I am trying to design an SQL statement that will delete the MIN value in one field.

I have three fields of interest:

Fields: Parcel, Soil_Texture, Area

Ex. Parcel #1 is duplicated three times b/c there are three Soil Ttextures and three Area associated with it:

i.e.

Parcel Soil_Texture Area
1 Loam 10
1 Silt 20
1 Clay 15

I would like my result to delete two records and keep the Silt with 20 Area. ***Keep in mind that there are thousands of Parcels with duplicate records such as the one above.


Regards~

 

[TheSQL]

I am trying to design an SQL statement that will delete the MIN value in one field.

Based upon your example, it looks like you're trying to delete all but the max area, for a given parcel. Is that correct? Running on that assumption, this would work. (We're using MyTable because I didn't see a table name).

DELETE MyTable
FROM MyTable
LEFT OUTER JOIN (SELECT Parcel, MAX(Area) AS MaxArea
                 FROM MYTABLE
                 GROUP BY Parcel) AS MaxSubQuery ON MyTable.Parcel = MaxSubQuery.Parcel AND MyTable.Area = MaxSubQuery.MaxArea
WHERE MaxSubQuery.MaxArea IS NULL

The subquery will return a list of each parcel, along with the MAX area for that parcel. LEFT OUTER JOIN that to MyTable, and you'll end up with NULLs anywhere the parcel's area in MyTable isn't equal to the max area. Make sense?

A question in my mind would be:
- What happens when two readings have the same area? Is it acceptable to delete all but two, or would you somehow need to arbitrarily delete all but one? The code above will leave multiple readings if there is a "tie".

 

[JarlH]

Just one minor comment, that DELETE with OUTER JOIN is not ANSI SQL at all. Is it perhaps SQL Server specific syntax?

The following statement is ANSI compliant (Core SQL-2003):
[tt]
DELETE FROM MyTable AS DelM
WHERE Area < (SELECT MAX(Area) FROM
MyTable WHERE DelM.parcel = parcel)
[/tt]

As TheSQL alreay said, duplicated max Area's for a Parcel will be kept. (And rows with NULL Areas as well.)

 

[TheSQL]

Not at all minor - good point. It's easy to forget which forum you're posting into!

 

[Hunguyen]

Thank you all for your assistance! It really helped me in pinch

An extra special thank you to JarlH for your script, which worked like a charm.

Cheers,
HUNGUYEN