SQL alias for SELECT statement

Question

I would like to do something like

(SELECT ... FROM ...) AS my_select WHERE id IN (SELECT MAX(id) FROM my_select GROUP BY name) 

Is it possible to somehow do the "AS my_select" part (i.e. assign an alias to a SELECT statement)?

(Note: This is a theoretical question. I realize that I can do it without assign an alias to a SELECT statement, but I would like to know whether I can do it with that.)

Answer 1

Not sure exactly what you try to denote with that syntax, but in almost all RDBMS-es you can use a subquery in the FROM clause (sometimes called an "inline-view"):

SELECT.. FROM (      SELECT ...      FROM ...      ) my_select WHERE ... 

In advanced "enterprise" RDBMS-es (like oracle, SQL Server, postgresql) you can use common table expressions which allows you to refer to a query by name and reuse it even multiple times:

-- Define the CTE expression name and column list. WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear) AS -- Define the CTE query. (     SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear     FROM Sales.SalesOrderHeader     WHERE SalesPersonID IS NOT NULL ) -- Define the outer query referencing the CTE name. SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear FROM Sales_CTE GROUP BY SalesYear, SalesPersonID ORDER BY SalesPersonID, SalesYear; 

(example from http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx)

Answer 2

You can do this using the WITH clause of the SELECT statement:

; WITH my_select As (SELECT ... FROM ...)  SELECT * FROM foo WHERE id IN (SELECT MAX(id) FROM my_select GROUP BY name) 

That's the ANSI/ISO SQL Syntax. I know that SQL Server, Oracle and DB2 support it. Not sure about the others...