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

User · Accepted Answer

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)

User · Answer

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

User · Answer

Yes  but you can select only one column in your subselect  SELECT  SELECT id FROM bla  AS my select FROM bla2

User · Answer

You could store this into a temporary table   So instead of doing the CTE sub query you would use a temp table   Good article on these here http   codingsight com introduction-to-temporary-tables-in-sql-server

[sql] SQL alias for SELECT statement

Examples related to sql

Examples related to select

Examples related to alias