CODEWITHSUNDEEP
sqlsql-server-2008
user3124690
user3124690

Reputation: 433

ORDER BY issue in Select distinct case

SELECT DISTINCT
    CAST(dpt.TrackId AS varchar(20)) + ' , ' + CONVERT(VARCHAR(19), dpt.CreatedDate) AS TrackId  
FROM         
    ExcelUtilityTemplate 
INNER JOIN        
    DataImportProcessTracker dpt ON ExcelUtilityTemplate.TemplateId = dpt.TemplateId        
INNER JOIN
    TA_JDSU..Employee_Temp et ON et.TrackId = dpt.TrackId        
WHERE     
    (ExcelUtilityTemplate.ClientId = 'e0a94231-3265-4277-9cc3-236d9a2ead49' 
     AND et.RowStatus = 1) 
ORDER BY 
    dpt.CreatedDate desc

My above query throws the following error:

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

I want to sort my records on the basis of CreatedDate.

Hoping for quick reply. Thanks

Upvotes: 3

Views: 975

Answers (2)

Sai Avinash
Sai Avinash

Reputation: 4753

Modify your query as follows:

  SELECT distinct  Cast(dpt.TrackId as varchar(20)) +' , '+ CONVERT(VARCHAR(19),dpt.CreatedDate) as TrackId ,dpt.CreatedDate
    FROM         ExcelUtilityTemplate INNER JOIN        
                          DataImportProcessTracker dpt ON ExcelUtilityTemplate.TemplateId = dpt.TemplateId        
                          inner join TA_JDSU..Employee_Temp et on et.TrackId=dpt.TrackId        
    WHERE     (ExcelUtilityTemplate.ClientId = 'e0a94231-3265-4277-9cc3-236d9a2ead49' and et.RowStatus=1) order by dpt.CreatedDate desc

Explanation:

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

When ever you are using distinct with select and ordering by some field. Then , That field should be included in your select clause

Hope this helps..

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

You'll need to order by the same value as you're doing DISTINCT on, since otherwise you may end up with more than one value to order by for each row;

SELECT distinct CAST(dpt.TrackId as varchar(20)) +' , '+ 
                CONVERT(VARCHAR(19),dpt.CreatedDate) as TrackId  
FROM ExcelUtilityTemplate 
INNER JOIN DataImportProcessTracker dpt 
  ON ExcelUtilityTemplate.TemplateId = dpt.TemplateId        
INNER JOIN TA_JDSU..Employee_Temp et 
  ON et.TrackId=dpt.TrackId        
WHERE (ExcelUtilityTemplate.ClientId = 'e0a94231-3265-4277-9cc3-236d9a2ead49' 
  AND et.RowStatus=1) 
ORDER BY CAST(dpt.TrackId as varchar(20)) +' , '+ 
                CONVERT(VARCHAR(19),dpt.CreatedDate) DESC

Upvotes: 1

Related Questions