sql server - How to write sql for this case? -


suppose have table(a relationship) like

mytab(id1,id2,ismarked, data,....) 

the sample data maybe looks like:

1,  1, 1, ... 1,  2, 0, ... 1,  3, 0, ...  2,  34, 1, ...  3,  4, 0, ...  4,  546, 0, ... 4,  8, 0, ... 

only 1 marked each id1. want data marked 1 entities id1. if there no marked record, first 1 or 1 of them.

for above sample data, result should be:

1,  1, 1, ...  2,  34, 1, ...  3,  4, 0, ...  4,  546, 0, ... 

union solution, long , may have bad performance. idea sort data id1 , ismarked desc, first 1 each id1, how write sql case?

for only 1 marked each id1 following should work:

;with cte (     select *, rn=row_number() on (partition id1 order ismarked desc) ) select * cte rn=1 

Comments

Popular posts from this blog

Detect support for Shoutcast ICY MP3 without navigator.userAgent in Firefox? -

web - SVG not rendering properly in Firefox -

java - JavaFX 2 slider labelFormatter not being used -