| AgencyId | SourceType | SourceCode | PropertyState | Code |
|---|---|---|---|---|
| a1002 | NULL | Xyz1 | NULL | test1 |
| a1002 | NULL | Xyz2 | NULL | test2 |
| a1002 | NULL | Xyz3 | NULL | test3 |
| a1002 | NULL | Xyz4 | NULL | test4 |
| a1002 | NULL | Xyz5 | NULL | test5 |
| a1002 | NULL | Xyz6 | NULL | test6 |
| a1002 | NULL | Xyz7 | NULL | test7 |
| a1002 | NULL | Xyz8 | NULL | test8 |
| a1002 | NULL | Xyz9 | NULL | test9 |
| a1002 | NULL | Xyz10 | NULL | test10 |
| a1002 | NULL | Xyz11 | NULL | test11 |
| a1002 | NULL | Xyz12 | NULL | test12 |
| a1002 | NULL | Xyz13 | NULL | test13 |
| a1002 | NULL | Xyz14 | NULL | test14 |
| a1002 | NULL | Xyz15 | NULL | test15 |
| a1002 | NULL | Xyz16 | NULL | test16 |
| a1002 | NULL | Xyz17 | NULL | test17 |
| a1002 | NULL | Xyz18 | NULL | test18 |
| a1002 | NULL | Xyz19 | NULL | test19 |
| a1002 | NULL | Xyz20 | NULL | test20 |
| a1002 | NULL | Xyz21 | NULL | test21 |
| a1002 | NULL | Xyz22 | NULL | test22 |
| a1002 | NULL | Xyz23 | NULL | test23 |
| a1002 | NULL | Xyz24 | NULL | test24 |
| a1002 | NULL | Xyz25 | NULL | test25 |
| a1002 | NULL | Xyz26 | NULL | test26 |
| a1003 | NULL | Xyz27 | FL | test27 |
| a1003 | NULL | Xyz28 | NULL | test28 |
| a1004 | NULL | NULL | NULL | test29 |
| a1005 | NULL | NULL | NULL | test30 |
| a1006 | NULL | NULL | FL | test31 |
| a1006 | NULL | NULL | NULL | test32 |
| a1007 | NULL | NUL**L | NULL | test33 |
| a1008 | B | NULL | NULL | test34 |
| a1008 | O | NULL | NULL | test35 |
I have a table name test,in that there are 5 columns AgencyId,SourceType,SourceCode,PropertyStae,Code
Want to write sql query which will give Code as output base on following drilldown logic
First match by AgencyId,SourceCode,PropertyState if not then AgencyId,SourceCode if not then AgencyId,SourceType if not then AgencyId PropertyStae if not then SourceType PropertyState if not then only by AgencyId.
How to write sql query for this. Requirement is such that can not write sp nor functions. Kindly let me know about the solution