sql - Eliminating duplicate rows except one column with condition -

i having trouble trying find appropriate query(sql-server) selecting records condition however, table using has more 100,000 rows , more 20 columns.

so need code satisfies following condition:

1.)if [policy] , [plan] column unique between rows select record

2.)if [policy] , [plan] return 2 or more rows select record 'code' column isn't 999

3.)in cases unwanted rows may not have '999' in [code] column may other specifics

in other words, row number 1,2,4,5,7.

here example of table looks like

row #|policy|plan|code -----------------------   1  |    | aa |111 -----------------------   2  |  b   | bb |112 -----------------------   3  |  b   | bb |999 -----------------------   4  |  c   | cc |111 -----------------------   5  |  c   | cc |112 -----------------------   6  |  c   | cc |999 -----------------------   7  |  d   | dd |999 ----------------------- 

i'm expecting see like

row #|policy|plan|code -----------------------   1  |    | aa |111 -----------------------   2  |  b   | bb |112 -----------------------   4  |  c   | cc |111 -----------------------   5  |  c   | cc |112 -----------------------   7  |  d   | dd |999 ----------------------- 

thank in advance

may want (eliminate last row if more one)?

select t.* (select t.*             , row_number() on (partition policy, plan                           order code desc                          ) rn             , count(*) on (partition policy, plan) rc       t      ) t rn > 1 or rn=rc; 

output:

    row policy  plan    code    rn  rc 1   1     aa  111 1   1 2   2   b   bb  112 2   2 3   5   c   cc  112 2   3 4   4   c   cc  111 3   3 5   7   d   dd  999 1   1