Selecting a specific value from 3 different colums under different rules minimum or middle in SQL server -

i have (hopefully) little problem in sql need query single value 3 columns in new column.

here table looks :

name of issuer     #ofratings    agency1    agency2    agency3 aaa                        3            22         22         24          bbb                        3            22         24         28  ccc                        2            16         12         null   ddd                        2            16         16         null  eee                        1            null        3         null     

now problem... i'll try has clear possible feel free tell me if unclear.

i need create new column bring resulting agency value following these rules

1. if have 3 agency quotes , none identical, want middle 1 (issuer bbb in example).
2. if have 3 agency quotes , 2 identical want 1 in new column (issuer aaa in example.
3. if have 2 agency quotes , identical, want either brought in new column (issuer ddd in example).
4. if have 2 agency quotes , both different want minimum between 2(issuer ccc in example).
5. if have 1 agency quote want 1 brought (issuer eee in example).

mind can has have agency 1 , 3 identical in issuer aaa , forth.

i did search on getting min values 3 columns , got fou what's best way select minimum value several columns?

the problem tried case when example given sql allows maximum of 10 levels on nesting in case argument , rule makes me bust :s

any appreciated.

thank you

edit @fabien solution

here select function in question mind s&p agency 1 in example, moodys agency2 , dbrs agency3

   select        no.issuer_cd,  count(distinct(no.rater_cd)) 'norating', sp.rating_rank 's&p', mo.rating_rank 'moodys', db.rating_rank 'dbrs'         csm_issuer_rating r left join  (select          no.issuer_cd,     no.rater_cd       csm_issuer_rating no     no.rater_cd in ('m_sp_bond','m_dbrs_bond','m_moody_bond')) no on          r.issuer_cd = no.issuer_cd left join  csm_issuer on i.issuer_cd = no.issuer_cd  left join csm_rater_rating rr on rr.rater_cd = no.rater_cd  left join (   select   r.issuer_cd,                             r.rating_cd, rr.rating_rank         csm_issuer_rating r left join csm_rater_rating rr on rr.rating_cd = r.rating_cd                     rr.rater_cd = 'm_sp_bond' , r.rater_cd = 'm_sp_bond'               ) sp on          i.issuer_cd = sp.issuer_cd  left join             (   select   r.issuer_cd,                             r.rating_cd, rr.rating_rank         csm_issuer_rating r left join csm_rater_rating rr on rr.rating_cd = r.rating_cd                     rr.rater_cd = 'm_moody_bond' , r.rater_cd = 'm_moody_bond'               ) mo on          i.issuer_cd = mo.issuer_cd  left join             (   select   r.issuer_cd,                             r.rating_cd, rr.rating_rank         csm_issuer_rating r left join csm_rater_rating rr on rr.rating_cd = r.rating_cd                     rr.rater_cd = 'm_dbrs_bond' , r.rater_cd = 'm_dbrs_bond'               ) db on          i.issuer_cd = db.issuer_cd  group  no.issuer_cd, sp.rating_rank, mo.rating_rank, db.rating_rank) 

sql fiddle

ms sql server 2008 schema setup:

create table table1     (issuer_cd varchar(3), norating int,       "s&p" varchar(4), moodys varchar(4), dbrs varchar(4)) ;  insert table1     (issuer_cd, norating, "s&p", moodys, dbrs) values     ('aaa', 3, '22', '22', '24'),     ('bbb', 3, '22', '24', '28'),     ('111', 3, '26', '24', '28'),     ('ccc', 2, '16', '12', null),     ('ddd', 2, '16', '16', null),     ('eee', 1, null, '3', null) ; 

query 1:

select issuer_cd,         case when ("s&p" not null ,                  moodys not null) or                  ("s&p" not null ,                  dbrs not null) or                  (moodys not null ,                  dbrs not null)  (                    case when "s&p" between isnull(moodys,-1) ,                                               dbrs "s&p"                         when moodys between isnull("s&p",-1) ,                                               dbrs moodys                         when dbrs between isnull("s&p",-1) ,                                               moodys dbrs                         when "s&p" between isnull(dbrs,-1) ,                                               moodys "s&p"                         when moodys between isnull(dbrs,-1) ,                                               "s&p" moodys                         when dbrs between isnull(moodys,-1) ,                                               "s&p" dbrs                         when "s&p" = moodys or                               moodys = dbrs moodys                         when "s&p" = dbrs dbrs                    end                  )             when ("s&p" null ,                  moodys null) dbrs             when (moodys null ,                  dbrs null) "s&p"             when ("s&p" null ,                  dbrs null) moodys        end table1 

results:

| issuer_cd | column_1 | |-----------|----------| |       aaa |       22 | |       bbb |       24 | |       111 |       26 | |       ccc |       12 | |       ddd |       16 | |       eee |        3 | 

edit :

may can try :

if object_id(n'tempdb..#mytemp', n'u') not null  drop table #mytemp;  /* select */    select        no.issuer_cd,  count(distinct(no.rater_cd)) 'norating', sp.rating_rank 's&p', mo.rating_rank 'moodys', db.rating_rank 'dbrs' #mytemp ...  /* select */ select .... #mytemp;