Return Distinct Records by Category Based Multiple Column Values ID    MODEL    MODELSTATUS    CONTROL
11     100      GOOD           XN
24     100      TRENDING       BF
33     101      GOOD           XN
46     102      BAD            BF
50     103      BAD            XN
64     103      BAD            BF
77     104      PENDING        XN
89     104      TRENDING       BF
92     105      TRENDING       BF
93     105      TRENDING       XN


Given the data above, how would I return the results below.  There can either be 1 or 2 (at most) records with the same MODEL.  If there are 2 records for a MODEL the CONTROL will be different.

If MODESTATUS is 'Trending' return that record.  Else if MODELSTATUS is 'Pending' or 'Bad' return that record.  Else if there are 2 records for a MODEL where MODELSTATUS are the same return record with CONTROL is 'XN'

ID    MODEL    MODELSTATUS    CONTROL
24    100      TRENDING       BF
33    101      GOOD           XN
46    102      BAD            BF
50    103      BAD            XN
77    104      PENDING        BF
93    105      TRENDING       XN
This query gives the required output:

WITH data AS(
    SELECT [ID], [MODEL], [MODELSTATUS], [CONTROL]
        , r = ROW_NUMBER() OVER(PARTITION BY [MODEL] ORDER BY
            CASE WHEN MODELSTATUS = 'TRENDING' THEN 1 ELSE 0 END DESC
            , CASE WHEN MODELSTATUS IN ('PENDING', 'BAD') THEN 1 ELSE 0 END DESC
            , CASE WHEN [CONTROL] = 'XN' THEN 1 ELSE 0 END DESC
            , [ID] DESC
        )
    FROM @Table1
)
SELECT [ID], [MODEL], [MODELSTATUS], [CONTROL]
FROM data
WHERE r = 1;


See this SQL Fiddle. 

I assume that this is only a sample table with easy to understand char values and real data is using int instead of varchar for status and control. 

In term of performance and IO in real life, if this type of table gets bigger, it is wise to improve the design and replace varchar by tinyint ids for each Control and Status.

Output

ID  MODEL   MODELSTATUS CONTROL
24  100     TRENDING    BF
33  101     GOOD        XN
46  102     BAD         BF
50  103     BAD         XN
89  104     TRENDING    BF
93  105     TRENDING    XN