sql - How to select a winning records based on a priority field -

i have table has following columns:

eid(pk), memberid(pk), ename, priority, qualified

there can multiple records particular eid, memberid combination each different priority, this

1234, jsmith, news,         1, null 2345, jsmith, reactivation, 2, null 

i need write query evaluate rows in table , assign qualified column based on priority, results be

1234, jsmith, news,         1, y 2345, jsmith, reactivation, 2, n 

so complicate matters table have rows added through out day , each time new set of data added need rerun prioritization query.

an annoying note, cannot use cursors, variables or temp tables this. can create supporting tables , copy data them on temporary basis physical tables.

any appreciated. if description of problem sucks, apologize, let me know how can more explanatory , best.

thanks

if understand question correctly, think sql give want

update mytable set qualified='n' update mytable set mytable.qualified = 'y' mytable inner join (select memberid, min([priority]) [priority] mytable group memberid) t2 on t2.memberid=mytable.memberid , t2.priority = mytable.priority 

give shot , see if have understood requirements correctly