A SELECT statement can consist of the following basic clauses.

• SELECT
• INTO
• FROM
• JOIN
• WHERE
• GROUP BY
• HAVING
• UNION
• ORDER BY
• LIMIT

SELECT Syntax

The following syntax diagram outlines the syntax supported by the Microsoft Dynamics CRM adapter:

SELECT {
  [ TOP <numeric_literal> ]
  { 
    * 
    | { 
        <expression> [ [ AS ] <column_reference> ] 
        | { <table_name> | <correlation_name> } .* 
      } [ , ... ] 
  }
  [ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
  { 
    FROM <table_reference> [ [ AS ] <identifier> ] 
  } [ , ... ]
  [ [ 
      INNER | { { LEFT | RIGHT | FULL  } [ OUTER ] }  
    ] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ] 
  ] [ ... ] 
  [ WHERE <search_condition> ]
  [ GROUP BY <column_reference> [ , ... ]
  [ 
    ORDER BY 
    <column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
  ]
  [
    LIMIT <expression>
    [ 
      { OFFSET | , }
      <expression> 
    ]
  ] 
} | SCOPE_IDENTITY() 

  <expression> ::=
    | <column_reference>
    | @ <parameter> 
    | ?
    | COUNT( * | { [ DISTINCT ] <expression> } )
    | { AVG | MAX | MIN | SUM | COUNT } ( <expression> ) 
    | NULLIF ( <expression> , <expression> ) 
    | COALESCE ( <expression> , ... ) 
    | CASE <expression>
        WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
    [ ELSE { <expression> | NULL } ]
      END 
    | <literal>
    | <sql_function> 

  <search_condition> ::= 
    {
      <expression> { = | != | <> | < | > | <= | >= | IS NULL | IS NOT NULL | CONTAINS | LIKE | NOT LIKE | IN | NOT IN | AND | OR } [ <expression> ]
    } [ { AND | OR } ... ]

Examples

1. Return all columns:
   

   SELECT * FROM Lead

2. Rename a column:
   

   SELECT "FirstName" AS MY_FirstName FROM Lead

3. Cast a column's data as a different data type:
   

   SELECT CAST(Revenue AS VARCHAR) AS Str_Revenue FROM Lead

4. Search data:
   

   SELECT * FROM Lead WHERE FirstName <> 'Bob'

5. The Microsoft Dynamics CRM APIs support the following operators in the WHERE clause: =, !=, <>, <, >, <=, >=, IS NULL, IS NOT NULL, CONTAINS, LIKE, NOT LIKE, IN, NOT IN, AND, OR.
   

   SELECT * FROM Lead WHERE FirstName <> 'Bob';

6. Return the number of items matching the query criteria:
   

   SELECT COUNT(*) AS MyCount FROM Lead 

7. Return the number of unique items matching the query criteria:
   

   SELECT COUNT(DISTINCT FirstName) FROM Lead 

8. Summarize data:
   

   SELECT FirstName, MAX(Revenue) FROM Lead  GROUP BY FirstName

   See Aggregate Functions for details.
9. Retrieve data from multiple tables.
   

   SELECT Account.Id, Account.Name, Equipment.Id AS Eid, Equipment.Name AS Ename FROM Account JOIN Equipment ON Equipment.Id = Account.PreferredEquipmentId_Id

   See JOIN Queries for details.
10. Sort a result set in ascending order:
    

    SELECT Id, FirstName FROM Lead  ORDER BY FirstName ASC