CODEWITHSUNDEEP
sqlsql-serversql-order-bydistinct
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

ORDER BY and DISTINCT in same select statement

I have calculated a daily figure that brings together all figures entered across each name so i have duplicate rows under each name. I want the results to show only one result per user so i figured that it was best to use distinct. However, i also need to display the results by descending figure. Is this possible to do in the same statement?

    SELECT DISTINCT
    D.DATE, 
    COALESCE( M.[Team ADJ],D.[Team ADJ], M.[Name]) AS 'Sales Person',
    COALESCE( D.[Team], M.[Team]) AS 'Sales Team',
    isnull(D.[Daily Figure],0) AS [Daily Figure],

    FROM  
    [Daily] D
    FULL OUTER JOIN
    [Month] M ON D.[Name] = F.[Name]
    ORDER BY [Daily Figure] DESC

Current results

 Date       Sales Person    Sales Team     Daily Figure    Month 
 2017-09-19     FRED         SAM               20           285
 2017-09-19     FRED         SAM               20           285
 2017-09-19     FRED         SAM               20           285
 2017-09-19     TOM          BEN               140          118
 2017-09-19     TOM          BEN               140          118

ERROR:'ORDER BY items must appear in the select list if SELECT DISTINCT is specified.' Even though i have included it in the select list

Upvotes: 1

Views: 206

Answers (3)

SqlZim
SqlZim

Reputation: 38023

In the order by section, you can reference the column by its alias, e.g.:

select distinct 
    [Date]
  , coalesce( M.[Team adj],D.[Team adj], M.[Name]) as [Sales Person]
  , coalesce( D.[Team], M.[Team]) as [Sales Team]
  , isnull(D.[Daily Figure],0) as [Daily Figure]
  , [Month]
from  [Daily] D
  full outer join [Month] M 
    on D.[Name] = F.[Name]
order by [Daily Figure] desc

Without the source tables to build the query, here is a demo using your sample above:

select distinct
    t.Date, 
    [Sales Person],
    [Sales Team],
    isnull(t.[Daily Figure],0) AS [Daily Figure]
from t
order by [Daily Figure] desc

rextester demo: http://rextester.com/PVPHSR35614

returns:

+------------+--------------+------------+--------------+
|    Date    | Sales Person | Sales Team | Daily Figure |
+------------+--------------+------------+--------------+
| 2017-09-19 | tom          | ben        |          140 |
| 2017-09-19 | fred         | sam        |           20 |
+------------+--------------+------------+--------------+

Upvotes: 1

Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

The best way to resolve this issue is to order by the column number position. E.G. If the column is in the 4th position 'order by 4'

SELECT DISTINCT 
COALESCE( M.[Team ADJ],D.[Team ADJ], M.[Name]) AS 'Sales Person',
COALESCE( D.[Team], M.[Team]) AS 'Sales Team',
isnull(D.[Daily Figure],0),

FROM  
[Daily] D
FULL OUTER JOIN
[Month] M ON D.[Name] = F.[Name]
ORDER BY 4 DESC

Upvotes: 0

Wes H
Wes H

Reputation: 4439

The sample query did not generate the current results you've posted.

In a Distinct Order By, the order column must be part of the result set. In your sample query, there is not a column named Daily Figure. If Daily Figure was used without an expression, the Order By would work fine. However, once you wrap it in an expression, it is no longer named Daily Figure.

The simplest fix is to alias the column. 

, isnull(D.[Daily Figure],0) as [Daily Figure]

* Update *

Here is a snippet you can run on any SQL Server showing Distinct Order By in action.

SELECT DISTINCT
            t.type_desc,
            CAST(t.create_date AS DATE) AS Created
  FROM      sys.tables AS t
  ORDER BY  Created
;

Upvotes: 0

Related Questions