SQL Server - Finding the Highest Priority item using Bitwise operators

Question

The Problem:

I want to return all of the rows from the Primary Data table together with the Highest Priority Exception based on the currently assigned Priority in an Exception table.

I have created a simplified example of my data set-up below (with creation scripts) so hopefully you can help with what should be a fairly quick T-SQL problem.

The setup:

I have a primary data table where each row can have one or more exceptions stored as a bit mask.

 CREATE TABLE [dbo].[PrimaryData](
    Id [INT] IDENTITY(1,1) NOT NULL,
    SomeData [VARCHAR](30) NOT NULL,
    Exceptions [INT] NOT NULL,
 )
 INSERT INTO [dbo].[PrimaryData](SomeData, Exceptions)
    VALUES('Data A', 0)
 INSERT INTO [dbo].[PrimaryData](SomeData, Exceptions)
    VALUES('Data B', 6)
 INSERT INTO [dbo].[PrimaryData](SomeData, Exceptions)
    VALUES('Data C', 6)
 INSERT INTO [dbo].[PrimaryData](SomeData, Exceptions)
    VALUES('Data D', 192)
 INSERT INTO [dbo].[PrimaryData](SomeData, Exceptions)
    VALUES('Data E', 132)

The Exceptions are stored in a lookup table purely because each of them is given a user assigned priority. This table cannot have rows added or deleted by the end user they just have control of the priority of each exception with 1 being the highest.

CREATE TABLE [dbo].[Exception](
    Id [INT] IDENTITY(1,1) NOT NULL,
    Priority [INT] NOT NULL, 
    Mask [SMALLINT] NOT NULL,
    Description [VARCHAR](30) NOT NULL
 )
 INSERT INTO [dbo].[Exception] (Priority, Mask, Description)
      VALUES(1, 1, 'Exception A')
 INSERT INTO [dbo].[Exception] (Priority, Mask, Description)
      VALUES(2, 2, 'Exception B')
 INSERT INTO [dbo].[Exception] (Priority, Mask, Description)
      VALUES(3, 4, 'Exception C')
 INSERT INTO [dbo].[Exception] (Priority, Mask, Description)
      VALUES(4, 8, 'Exception D')
 INSERT INTO [dbo].[Exception] (Priority, Mask, Description)
      VALUES(5, 16, 'Exception E')
 INSERT INTO [dbo].[Exception] (Priority, Mask, Description)
      VALUES(6, 32, 'Exception F')
 INSERT INTO [dbo].[Exception] (Priority, Mask, Description)
      VALUES(7, 64, 'Exception G')
 INSERT INTO [dbo].[Exception] (Priority, Mask, Description)
      VALUES(8, 128, 'Exception H')
 INSERT INTO [dbo].[Exception] (Priority, Mask, Description)
      VALUES(9, 256, 'Exception I')

So based on the sample data supplied I want to return SomeData, Mask (of highest priority) and Description (of highest priority).

i.e.

| Data B | 2 | Exception B

Obviously I need to do this in the most efficient way possible as there could be 25K rows being returned in the Primary Data Table.

Thanks in advance.

Answer 1

SELECT  *
FROM    PrimaryData pd
CROSS APPLY
        (
        SELECT  TOP 1 *
        FROM    Exception e
        WHERE   e.Mask & pd.Exceptions <> 0
        ORDER BY
                e.Priority
        ) q

Answer 2

This will get what you want for a single PrimaryData row.

select top 1 SomeData, Mask
  from PrimaryData
    inner join Exceptions
      on (PrimaryData.Exceptions & Exceptions.Mask <> 0)
  where PrimaryData.Id = 27
  order by Priority

For all the rows, something like this should work (edited as suggested by Quassnoi)

with data as (
  select SomeData, Mask, row_number() over
      (partition by PrimaryData.Id order by Priority) AS row
    from PrimaryData
      inner join Exceptions
        on (PrimaryData.Exceptions & Exceptions.Mask <> 0)
)
select * 
  from data
  where row = 1

Edited to change | to &