Our resident architect came over today and posed me this little puzzler – he wanted a query that would return if 1 row or nothing based of a certain criteria. In other words if two rows met the criteria he wanted nothing pulled back.

A strange request I’ll grant ye, and one that took me some time to bang my head against, however the answer was pretty neat:

Create Schema Test;
Go

Create Table Test.Only1RowTest
(
         Key1    Int
        ,Key2    Int
)
Go

Insert into Test.Only1RowTest Values (1,1),(2,2),(3,3),(1,2);


-- Demonstrate pulling back no rows because 2 rows meet the criteria

With Cte As ( Select Top(2) * From Test.Only1RowTest where Key1 = 1  )
    Select Cte.* from Cte Inner Join  ( Select Count(*) As c From Cte) As CteC On CteC.c = 1;

-- Expand the criteria to further limit the row count and we get 1

With Cte As ( Select Top(2) * From Test.Only1RowTest where Key1 = 1 And Key2 = 1 )
    Select Cte.* from Cte Inner Join  ( Select Count(*) As c From Cte) As CteC On CteC.c = 1;

To break it down further, we create a common table expression and start off on the basis that returning 2 rows is just as bad as returning 2 million, so we limit the rows pulled back with a Top(2), we then bring back the results of the CTE as long as the # of rows returned =1 (no-one ever said an Inner Join had to join two columns together).

Sql Server 2008 Build 10.0.1798.0 (RTM-CU4)