Selecting from sub-queries as tables

I am sure this trick of using sub-queries in a FROM clause is known to many of you.  I ran across a need to use a sub-query in a FROM clause recently at work.  I remember before I learned the trick I was very frustrated that I couldn't get it to work.  So I decided to write this short article to help out any that may not have run across this trick to use sub-queries in a FROM clause.

First let's set up a table and some data to work with.

Create Table orders
(id uniqueidentifier not null primary key,
 OrderNbr varchar(50) not null,
 OrderLineNbr varchar(50) not null,
 Quantity numeric(8,2) not null,
 StatusCode varchar(20) not null,
 UpdateDate datetime not null)

Now we will insert some data.

Insert into orders (id,OrderNbr, OrderLineNbr, Quantity, StatusCode,UpdateDate)
 Values(NEWID(),'1','1',1.0,'Closed','2014-12-01'),
  (newid(),'1','2',2.0,'InProcess', '2014-12-10'),
  (newID(),'1','3',2.0,'Open','2014-12-31'),
  (newID(),'2','1',1.5,'Open','2014-01-02'),
  (newID(),'3','1',3.0,'InProcess','2014-12-28')

I know some might complain about using a uniqueidentifier as the primary key, but that is not what this article is about.  I ran across a need to choose a min date verses a max date for an order number based off the status code of each line.  I am sure there are more elegant ways to solve this problem, but I ended up using two sub-queries in the FROM clause that were left joined.

Now that we have data in the table let's look at a sub-query that won't work.

Select *
  FROM
  (Select OrderNbr, max(UpdateDate) as UpdateDate FROM Orders
   Group by OrderNbr)

You will get an error something like:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.

So here is the trick.  You just need to add an alias to the sub-query.  This select will work:

Select *
  FROM
  (Select OrderNbr, max(UpdateDate) as UpdateDate FROM Orders
   Group by OrderNbr) t

If you are having trouble seeing the difference between the two queries it is the alias "t" that I added to the end of the second query.

You can join multiple sub-queries as long as they have an alias.

Select t.orderNbr, isnull(t2.minDate,t.UpdateDate) as UpdateDate
  FROM
  (Select OrderNbr, max(UpdateDate) as UpdateDate FROM Orders
   Group by OrderNbr) t
  left join (Select OrderNbr, min(UpdateDate) as minDate FROM Orders
   Where StatusCode <> 'Closed'
   Group by OrderNbr) t2 on t.OrderNbr = t2.OrderNbr

I hope you have enjoyed this short article.  If perhaps you didn't know how to get sub-queries in a FROM clause to work, now you should know how to do it.