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

Question

I have a (hopefully) little problem in SQL where I need to query back a single value from 3 columns in a new column.

Here is what my table looks like :

Name of the 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 my problem... I'll try to be has clear as possible feel free to tell me if I an unclear.

I need to create a new column where I would bring back the resulting agency value following these rules

IF I have 3 agency quotes and none are identical, then I want the middle one (Issuer BBB in the example).
If I have 3 agency quotes and 2 are identical then I want that one in the new column (Issuer AAA in the example.
If I have 2 agency quotes and they are identical, then I want either to be brought back in my new column (Issuer DDD in the example).
If I have 2 agency quotes and both are different then I want the minimum between the 2(Issuer CCC in the example).
If I have only 1 agency quote then I want that one brought back (Issuer EEE in the example).

Mind you I can just has easily have agency 1 and 3 be identical in the Issuer AAA and so forth.

I did a search on getting the min values from 3 columns and got fou What's the best way to select the minimum value from several columns?

The problem is I tried the CASE WHEN THEN example given but SQL allows a maximum of 10 levels on nesting in a case argument and my rule makes me bust that :S

Any help would be greatly appreciated.

Thank you

EDIT for @Fabien the Solution

HEre is the Select function in question Mind you that S&P is Agency 1 in my example, Moodys is agency2 and DBRS is agency3

   Select       
no.issuer_cd, 
count(distinct(no.Rater_CD)) as 'norating',
SP.rating_rank as 'S&P',
MO.rating_rank as 'Moodys',
DB.rating_rank as 'DBRS'

from        csm_issuer_rating r
left join 
(select     
    no.issuer_cd,
    no.rater_cd 

    from csm_issuer_rating no
    where no.rater_cd in ('M_SP_BOND','M_DBRS_BOND','M_MOODY_BOND')) as no
on          r.issuer_cd = no.issuer_cd
left join 
csm_issuer i 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

from        csm_issuer_rating r
left join csm_rater_rating rr on rr.rating_cd = r.rating_cd             

where       rr.rater_cd = 'M_SP_BOND' and r.rater_cd = 'M_SP_BOND'


            ) as SP
on          i.issuer_cd = sp.issuer_cd

left join
            (   select  
r.issuer_cd,                            
r.rating_cd,
rr.rating_rank

from        csm_issuer_rating r
left join csm_rater_rating rr on rr.rating_cd = r.rating_cd             

where       rr.rater_cd = 'M_MOODY_BOND' and r.rater_cd = 'M_MOODY_BOND'


            ) as MO
on          i.issuer_cd = mo.issuer_cd

left join
            (   select  
r.issuer_cd,                            
r.rating_cd,
rr.rating_rank

from        csm_issuer_rating r
left join csm_rater_rating rr on rr.rating_cd = r.rating_cd             

where       rr.rater_cd = 'M_DBRS_BOND' and r.rater_cd = 'M_DBRS_BOND'


            ) as DB
on          i.issuer_cd = db.issuer_cd

group by 
no.issuer_cd,
SP.rating_rank,
MO.rating_rank,
DB.rating_rank)

@GordonLinoff I am using Sql Server I don't think I can my accesses in the databases are pretty restricted. For example I cannot create any tables. I can pretty much use select statements and such. Thanks for taking the time to help me. — user2747281, Sep 06 '13 at 13:49

Fabien TheSolution · Answer 1 · 2013-09-06T15:29:34.037

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 INTO 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" is not null and
                 Moodys is not null) or
                 ("S&P" is not null and
                 DBRS is not null) or
                 (Moodys is not null and
                 DBRS is not null)  then (
                   CASE when "S&P" between isnull(Moodys,-1) and 
                                             DBRS then "S&P"
                        when Moodys between isnull("S&P",-1) and 
                                             DBRS then Moodys
                        when DBRS between isnull("S&P",-1) and 
                                             Moodys then DBRS
                        when "S&P" between isnull(DBRS,-1) and 
                                             Moodys then "S&P"
                        when Moodys between isnull(DBRS,-1) and 
                                             "S&P" then Moodys
                        when DBRS between isnull(Moodys,-1) and 
                                             "S&P" then DBRS
                        when "S&P" = Moodys or 
                             Moodys = DBRS then Moodys
                        when "S&P" = DBRS then DBRS
                   END
                 )
            when ("S&P" is null and
                 Moodys is null) then DBRS
            when (Moodys is null and
                 DBRS is null) then "S&P"
            when ("S&P" is null and
                 DBRS is null) then Moodys
       END
FROM Table1

Results:

| ISSUER_CD | COLUMN_1 |
|-----------|----------|
|       AAA |       22 |
|       BBB |       24 |
|       111 |       26 |
|       CCC |       12 |
|       DDD |       16 |
|       EEE |        3 |

EDIT :

May be you can try something like this :

IF OBJECT_ID(N'tempdb..#mytemp', N'U') IS NOT NULL 
DROP TABLE #mytemp;

/* Your SELECT */
   Select       
no.issuer_cd, 
count(distinct(no.Rater_CD)) as 'norating',
SP.rating_rank as 'S&P',
MO.rating_rank as 'Moodys',
DB.rating_rank as 'DBRS'
INTO #mytemp
FROM ...

/* My SELECT */
SELECT ....
FROM #mytemp;

Wow!!! Thank you very much it is very well written. There is just a little problem though and it comes from my restrictions in the access of the database. I cannot use the function create table... I just found that out. I had managed to make my table using various select statements along with left joins and group by. Though I did manage to include my query at the FROM line of yours and it did bring me something back and no errors. The only problem is that my Column_1 is populated only with Agency2 for some reason. Again Tank you Fabien TheSolution :) — user2747281, Sep 05 '13 at 13:14
My other comment was not clear I meant the resulting column is only populated with the name Agency2 not the number associated with it... — user2747281, Sep 05 '13 at 13:27
If you can add this 'SELECT' in the question, I will see if I can find the problem — Fabien TheSolution, Sep 05 '13 at 14:39
Have you tried to insert the result of this SELECT in a temporary table and then run mine on this temp table? See the edit on my answer. You are supposed to be able to do this even if you can't create table. I also edit my `SELECT` to reflect your aliases instead of `Agency...` — Fabien TheSolution, Sep 06 '13 at 15:31

score -1 · Answer 2 · answered Sep 04 '13 at 15:23

Replace, tablename with your table name :

select *, (case when agency1<>agency2 and agency1<>agency3 then agency2 
           when agency1=agency2 and agency1<>agency3 then agency3
           when agency1<>agency2 and agency3=agency2 then agency1
           when agency3<>agency2 and agency3=agency1 then agency2
           when agency1 is null and agency2=agency3 then agency2
           when agency2 is null and agency1=agency3 then agency1
           when agency3 is null and agency1=agency2 then agency1
           when agency1 is null and agency2<agency3 then agency2
           when agency1 is null and agency2>agency3 then agency3
           when agency2 is null and agency1<agency3 then agency1
           when agency2 is null and agency1>agency3 then agency3
           when agency3 is null and agency1<agency2 then agency1
           when agency3 is null and agency1>agency2 then agency2
           when agency1 is null and agency2 is null then agency3
           when agency2 is null and agency3 is null then agency1
           when agency1 is null and agency3 is null then agency2
           end)columnname
from tablename

Thank you very much for the help Sonam. Only problem is that my new column only brings me agency2 and not the number itself but the name agency2... Again thank you for the input. — user2747281, Sep 05 '13 at 13:19

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

2 Answers2