SQL Order by clause with example

 The order by clause is used to sort data in ascending or descending order.

SELECT (without ORDER BY) returns records in no particular order. To ensure a specific sort order use the ORDER BY clause

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

Order by Syntax:

SELECT column1, cloumn2, ....columnN 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

Example

USE AdventureWorks2017;  
GO  
SELECT ProductID, Name FROM Production.Product  
WHERE Name LIKE 'Lock Washer%'  
ORDER BY ProductID;

Best Practices:

• Avoid specifying integers in the ORDER BY clause as positional representations of the columns in the select list. For example, although a statement such as SELECT ProductID, Name FROM Production.Product ORDER BY 2 is valid, the statement is not as easily understood by others compared with specifying the actual column name. 
• Changes to the select list, such as changing the column order or adding new columns, requires modifying the ORDER BY clause in order to avoid unexpected results.
• In a SELECT TOP (N) statement, always use an ORDER BY clause. This is the only way to predictably indicate which rows are affected by TOP

Specifying ascending and descending sort order

Below is the example to sort data in ASC or DESC order. Both can be used in one order by clause.

USE AdventureWorks2017;  
GO  
SELECT ProductID, Name FROM Production.Product  
WHERE Name LIKE 'Lock Washer%'  
ORDER BY ProductID ASC;

USE AdventureWorks2017;  
GO  
SELECT ProductID, Name FROM Production.Product  
WHERE Name LIKE 'Lock Washer%'  
ORDER BY ProductID DESC;

Using ORDER BY with UNION, EXCEPT, and INTERSECT

When a query uses the UNION, EXCEPT, or INTERSECT operators, the ORDER BY clause must be specified at the end of the statement and the results of the combined queries are sorted.

USE AdventureWorks2017;  
GO  
SELECT Name, Color, ListPrice  
FROM Production.Product  
WHERE Color = 'Red'  
-- ORDER BY cannot be specified here.  
UNION ALL  
SELECT Name, Color, ListPrice  
FROM Production.Product  
WHERE Color = 'Yellow'  
ORDER BY ListPrice ASC;

SQL AND, OR, NOT Operator with example

 The WHERE clause can be combined with AND, OR and NOT operators

The AND and OR operators are used to filter records based on more than one condition:

• The AND operator displays a record if all the conditions separated by AND are TRUE.
• The OR operator displays a record if any of the conditions separated by OR is TRUE.
• The NOT operator displays a record if the condition(s) is NOT TRUE.

Lets go through one by one.

SQL AND Operator:

When multiple conditions are joined using AND operator, only those rows will be fetched from the database which meets all the conditions.

AND Syntax:

SELECT column_name1, column_name2, ...
FROM [table_name]
WHERE condition_1 AND condition_2 ...

	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

AND Operator Example:

SELECT BusinessEntityID,JobTitle,MaritalStatus FROM
    HumanResources.Employee
WHERE
    MaritalStatus = 'M'
AND JobTitle='Marketing Specialist'

Result:

SQL OR Operator:

When multiple conditions are joined using OR operator, all those rows will be fetched from the database which meet any of the given conditions.

SQL OR Operator Syntax:

SELECT column_name1, column_name2, ...
FROM [table_name]
WHERE condition_1 OR condition_2 ...

	TRUE	FALSE
TRUE	TRUE	TRUE
FALSE	TRUE	FALSE

Example:

SELECT BusinessEntityID,JobTitle,MaritalStatus FROM
    HumanResources.Employee
WHERE
    MaritalStatus = 'M'
AND JobTitle='Marketing Specialist'

Result

SQL NOT Operator

When multiple conditions are joined using OR operator, all those rows will be fetched from the database which meet any of the given conditions.

Syntax:

SELECT column_name1, column_name2, ...
FROM [table_name]
WHERE NOT CONDITION

Example:

SELECT BusinessEntityID,JobTitle,MaritalStatus FROM
    HumanResources.Employee
WHERE
   NOT MaritalStatus = 'M'

Result