Off topic, however worthy of keeping for posterity. From the Department of Homeland Security comes this press release from the Office of the Press Secretary, and a statement on just how serious the US Government is on eradicating the 2009 H1N1 ‘Swine Flu’ virus.
And just incase they redact their statement, here’s the most chilling section:
One hopes this was a simple typo!
Like this:
Be the first to like this post.
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:
Code Snippet
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)
Like this:
Be the first to like this post.
