January 02, 2013

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 DISTINCT
BusinessEntityID
FROM HumanResources.
Employee
ORDER BY BirthDate
;

-- If you are going to sort with
distinct 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.

5 comments:

  1. IS this for MS SQL server? I think it is fine with Oracle DB.

    ReplyDelete
    Replies
    1. I specifically wrote this for MS SQL Server may be its working fine with oracle in this way..

      Delete