There can be only 1
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:
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)
