DISTINCT and ORDER BY together

When we are using DISTINCT we can’t use ORDER BY as we want. If you are going to use DISTINCT when you have ORDER BY, all ODER BY attributes should be in SELECT phrase.

USE AdventureWorks2012;

-- Works fine
SELECT BusinessEntityID
FROM HumanResources.Employee
ORDER BY BirthDate;

-- Fails
SELECT DISTINCTBusinessEntityID
FROM HumanResources.Employee
ORDER BY BirthDate;

-- If you are going to sort withdistinct result should consist attribute that you are sorting
SELECT DISTINCT BusinessEntityID
FROM HumanResources.Employee
ORDER BY BusinessEntityID;

Otherwise it fails with following error.

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.