Today's question (by Steve Jones - SSC Editor):

Getting ANY of the data

-- Beer table
BeerIDBeerNamebrewer
5Becks    Interbrew
6Fat Tire   New Belgium
7Mac n Jacks  Mac & Jack's Brewery
8Alaskan AmberAlaskan Brewing 
9Kirin        Kirin Brewing

-- Beercount table
BeerName      BottleCount
Becks         5
Fat Tire      1
Mac n Jacks   2
Alaskan Amber 4
NULL          7
Corona        2
Tsing Tao     4
Kirin         12

SELECT *
FROM dbo.BeerCount AS bc
WHERE bc.BeerName=ANY
  (SELECT b2.BeerName FROM dbo.Beer AS b2);

Think you know the answer? Click here, and find out if you are right.

Subqueries II

What is wrong (if anything) with this code?

SELECT *
 FROM Sales.SalesOrderHeader AS soh 
 WHERE customerid IN (SELECT soh.CustomerID FROM Sales.Customer AS c WHERE soh.CurrencyRateID = 1 ORDER BY c.ModifiedDate)

Answer: There needs to be a TOP in the subquery

Explanation: A subquery can have an ORDER BY if there is a TOP clause (or OFFSET or FOR XML). Adding TOP lets this run. Now, the error is: Msg 1033, Level 15, State 1, Line 3 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. Ref: Subqueries - https://learn.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-ver16

Discuss this question and answer on the forums

Here's a few of the new posts today on the forums. To see more, visit the forums.