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.
IS this for MS SQL server? I think it is fine with Oracle DB.
ReplyDeleteI specifically wrote this for MS SQL Server may be its working fine with oracle in this way..
Delete