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 · 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

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 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