Select query select based on a priority

Question

Someone please change my title to better reflect what I am trying to ask.

I have a table like

Table (id, value, value_type, data)

ID is NOT unique. There is no unique key.

value_type has two possible values, let's say A and B.

Type B is better than A, but often not available.

For each id if any records with value_type B exists, I want all the records with that id and value_type B.

If no record for that id with value_Type B exists I want all records with that id and value_type A.

Notice that if B exists for that id I don't want records with type A.

I currently do this with a series of temp tables. Is there a single select statement (sub queries OK) that can do the job?

Thanks so much!

Additional details:

SQL Server 2005

Answer 1

RANK, rather than ROW_NUMBER, because you want ties (those with the same B value) to have the same rank value:

WITH summary AS (
  SELECT t.*,
         RANK() OVER (PARTITION BY t.id 
                          ORDER BY t.value_type DESC) AS rank
    FROM TABLE t
   WHERE t.value_type IN ('A', 'B'))
SELECT s.id,
       s.value,
       s.value_type,
       s.data
  FROM summary s
 WHERE s.rank = 1

Non CTE version:

SELECT s.id,
       s.value,
       s.value_type,
       s.data
  FROM (SELECT t.*,
               RANK() OVER (PARTITION BY t.id 
                                ORDER BY t.value_type DESC) AS rank
          FROM TABLE t
         WHERE t.value_type IN ('A', 'B')) s
 WHERE s.rank = 1

---

WITH test AS (
   SELECT 1 AS id, 'B' AS value_type
   UNION ALL
   SELECT 1, 'B'
   UNION ALL
   SELECT 1, 'A'
   UNION ALL
   SELECT 2, 'A'
   UNION ALL
   SELECT 2, 'A'),
     summary AS (
   SELECT t.*,
          RANK() OVER (PARTITION BY t.id 
                           ORDER BY t.value_type DESC) AS rank
     FROM test t)
SELECT *
  FROM summary
 WHERE rank = 1

I get:

id   value_type  rank
----------------------
1    B           1
1    B           1
2    A           1
2    A           1