Using the ORDER BY  clause

ORDER BY sorts rows in results for presentation purposes
     Use  of  ORDER BY  guarantees  the  sort  order of the result
     Last clause  to be logically  processed
     Sorts  all NULLs  together

ORDER BY can refer to:
     Columns by name, alias or ordinal position (not recommended)
     Columns not part of SELECT list unless DISTINCT  clause specified
Declare sort order with ASC  or  DESC

ORDER BY  clause examples

ORDER BY with column names:
 
     SELECT  SalesOrderID , CustomerID , OrderDate
     FROM  Sales . SalesOrderHeader
     ORDER BY OrderDate ;


ORDER BY with column alias:
 
      SELECT  SalesOrderID , CustomerID ,
                      YEAR(OrderDate)   AS  OrderYear
      FROM  Sales . SalesOrderHeader
     ORDER BY OrderDate ;


ORDER BY with descending order :
 
      SELECT  SalesOrderID , CustomerID , OrderDate
      FROM  Sales . SalesOrderHeader
      ORDER BY OrderDate  DESC ;

Filtering data in the WHERE clause

WHERE clause use predicates
      Must be expressed as logical conditions
      Only rows for which predicate evaluates to TRUE are accepted
      Value of FALSE  or  UNKNOWN are filtered out

WHERE clause follows FROM, precedes other claudes
    Can't see aliases declared in SELECT clause
    Can be optimized  by SQL Server use index

WHERE clause syntax

Filter rows for customers in territory 6

     SELECT  CustomerID, TerritoryID
     FROM  Sales.Customer
     WHERE  TerritoryID = 6 ; 

Filter rows for orders in territories greater than or equal to 6

     SELECT  CustomerID, TerritoryID
     FROM  Sales.Customer
     WHERE  TerritoryID   >= 6 ;

Filter orders within a range of dates

     SELECT  CustomerID, TerritoryID, StoreID
     FROM  Sales.Customer
     WHERE  StoreID  >=  1000  AND  StoreID  < = 1200 ;

Filtering data in the SELECT clause

Top  allows you to limit the number or percentage  of rows returned
Works with ORDER  BY clause to limit rows by sort order

      If ORDER BY list is not unique, results are not deterministic (no single correct result set)
      Modify ORDER BY list to ensure uniqueness, or use TOP WITH TIES

Added to SELECT clause:
     SELECT TOP (N) |  TOP (N) Percent
     With percent, number of rows rounded up

SELECT TOP (N) WITH TIES
       Retrieve duplicates where applicable (non-deterministic)
TOP is proprietary to MICROSOFT SQL Server

Filtering using TOP

Filter rows for customers to display top 20 TotalDue items

     SELECT  TOP  (20)  SalesOrderID,  CustomerID, TotalDue
     FROM    Sales.SalesOrderHeader
     ORDER BY  TotalDue  DESC ; 

Filter rows for customers to display top 20 TotalDue items with ties

    SELECT  TOP  (20)  WITH TIES SalesOrderID,  CustomerID, TotalDue
    FROM  Sales.SalesOrderHeader
    ORDER BY  TotalDue  DESC ; 

Filter rows for customers to display top 1% of  TotalDue items

    SELECT  TOP  (1)  PERCENT SalesOrderID,  CustomerID, TotalDue
    FROM   Sales.SalesOrderHeader
    ORDER BY  TotalDue  DESC ; 


Handling NULL in queries

Different components of SQL Sever handle NULL differently
      Query filters (ON, WHERE, HAVING) filter out UNKMOWNs
      CHECK constraints accept UNKMOWNS
     ORDER BY, DISTINCT treat NULLs  as  equals

Testing  for  NULL
     Use IS NULL  or IS NOT NULL rather than = NULL  or  < >  NULL

       SELECT CustomerID, StoreID, TerritoryID
       FROM Sales.Customer
       WHERE StoreID  IS  NULL
       ORDER  BY TerritoryID