Delete the duplicate state where the id is higher of the two values

id state
9 MA
10 MA
12 FL
13 TX

Basically it needs to delete the duplicate state row where id = 10.
I have tried subqueries but I am getting an error.

Is this what you’re looking for?

delete from table_name where id = 10;

Not sure what you mean by “higher” id that would delete more than just your duplicate.

delete from table_name where id > 9 and state in ('MA');

Thanks, but no.
Imagine that it is a very large data set and we don’t know which id or state has the duplicate before running the query.
The code show show the following steps.

  1. First, find records with duplicate value in the state field
  2. then delete the record with the higher value in the id column