how to convert this sql query to liquibase query

I have table like this

|-------------------------|
|   A   |   B   |    V    |
|-------------------------|
|   1   |   2   |    5    |
|-------------------------|
|   1   |   2   |    10   |
|-------------------------|
|   1   |   2   |    2    |
|-------------------------|

I need to delete all duplicated rows with equals A nad B value and lower C value

after running sql script i need to have only this row with top C Value for every equals A and B columns

|-------------------------|
|   A   |   B   |    V    |
|-------------------------|
|   1   |   2   |    10   |
|-------------------------|

this is the sql query i am using

delete from t where t.v < (select max(t2.v) from t t2 where t2.a = t.a and t2.b = t.b);

how to convert it to liquibase query

Answer

You mean something like this?

<changeSet  author="liquibase-docs"  id="delete-example">  
    <delete  catalogName="cat"  
            schemaName="public"  
            tableName="t">  
        <where>t.v &lt; (select max(t2.v) from t t2 where t2.a = t.a and t2.b = t.b)</where>  
    </delete>  
</changeSet>

Advertisement

Answer