Select Row Based on Column

I have a table with data like the following:

ID    Mfgr    Primary_Mfgr
a1    Acme    P
a1    Bcme    S
a1    Ccme    S
b1    Acme
b1    Bcme
c1    Acme    S
c1    Bcme
d1    Acme
d1    Bcme

I need to create a select statement that will, based on ID return the record with a P as the Primary_Mfgr, if there is no P record, then return the blank record, if those don't exist then return the first S record. In all cases, if there are duplicates return the first result.

Therefore, using the above data I would expect to return the following from four different queries:

Query for a1:

a1    Acme    P

Query for b1:

b1    Acme

Query for c1:

c1    Bcme

Query for d1:

d1   开发者_StackOverflow社区 Acme

In every case I need to return just one record. I'm not sure how I would structure to do this. I can easily get back all the 'a1' records for example, but I'm not sure how I would write a query that can take a parameter for ID and still return the correct single record in all circumstances. I'm using SQL Server 2008 R2.

You can just order by the Primary_Mfgr column with a case statement and then select the top 1

SELECT TOP 1 *
FROM Table1
WHERE ID = @ID
ORDER BY 
    CASE Primary_Mfgr 
        WHEN 'P' THEN 1
        WHEN ''  THEN 2
        ELSE 3
    END

This takes the "first" Mfgr alphabetically (subject to collation etc) where there is more than one for given ID/Primary_Mfgr pair. Otherwise you can change the secondary sort to another column (of you have one) or leave it out to get a random record.

;WITH myCTE AS
(
    SELECT
       *,
       ROW_NUMBER() OVER (
               PARTITION BY ID
               ORDER BY
                  CASE Primary_Mfgr 
                     WHEN 'P' THEN 1 WHEN 'S' THEN 3 ELSE 2
                  END, Mfgr) AS rn
    FROM
       MyTable
)
SELECT *
FROM myCTE
WHERE rn = 1

Edit: you don't have to query once per ID which is silly: this answer does it for all IDs in one go. So your 4 queries above become one.