Oracle clears duplicate data for a field (selects the maximum of another field period in duplicate data)

Requirements: Asset repair table in the same asset may be repaired to continue to apply for maintenance, this time the maintenance status needs to determine the maintenance status according to the most recent repair times, so duplicate data under the same Asset ID (Maintenance approval, maintenance approval failed), or may not appear (not apply for repair), Therefore, you need to query the asset repair table for the data that is not duplicated in the data and duplicate data in the request for repair date, the method is as follows:

The asset table is as follows:

1, all the data in the maintenance of assets
SELECT * FROM Asset_maintain t

The results are as follows:

2. Data that may be the same in asset maintenance
Select A.* from Asset_maintain a inner joins Asset_maintain B on a.asset_id=b.asset_id and A.rowid!=b.rowid

The results are as follows:

3. One of the largest storage dates in a query for equal data
SELECT * from Asset_maintain where maintain_start_date in (select Max (n.maintain_start_date) from Asset_maintain N Group B Y n.asset_id having count (n.asset_id) > 1)

The results are as follows:

4. Querying all data in the maintenance of assets (excluding the same data)
SELECT * from Asset_maintain t where t.asset_id not in (select a.asset_id from Asset_maintain a inner join Asset_maintain B on a.asset_id=b.asset_id and A.rowid!=b.rowid)

Finally, two subqueries of the same field, different data are associated:

(SELECT * from Asset_maintain t where t.asset_id not in (select a.asset_id from Asset_maintain a inner join Asset_maintain B on a.asset_id=b.asset_id and A.ROWID!=B.ROWID), UNION ALL
(SELECT * from Asset_maintain where maintain_start_date in (select Max (n.maintain_start_date) from Asset_maintain N Group by N.ASSET_ID have count (n.asset_id) > 1))

The results are as follows:

Oracle clears duplicate data for a field (selects the maximum of another field period in duplicate data)