CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Mr doubt
Mr doubt

Reputation: 65

SQL select query returning wrong order by DESC

This is my query which is not returning the correct result ordered by RegistrationDate Desc:

SELECT
    Team,
    CONVERT(VARCHAR(10), RegistrationDate, 103) AS RegistrationDate,
    FormFilledAt, CreationBy 
FROM
    Table_Candidate_Info 
WHERE
    Status = 'Completed'  
GROUP BY
    Team, CONVERT(VARCHAR(10), RegistrationDate, 103), FormFilledAt, CreationBy 
ORDER BY 
    RegistrationDate DESC

If I will use this query, it's returning the correct order by RegistrationDate Desc

select * 
from Table_Candidate_Info 
order by RegistrationDate desc

I want above first query should be RegistrationDate order by Desc with group by query

Upvotes: 1

Views: 1149

Answers (3)

Deepshikha
Deepshikha

Reputation: 10284

The reason why results are not ordered by RegistrationDate when you convert it to a varchar in Select clause is because Order By clause is logically processed after evaluation of Select clause. Now in first query when you write

Select * from Table_Candidate_Info order by RegistrationDate desc

[Though writing * in select list is a very bad practice] format of RegistrationDate still remains date in Select clause which holds true for further logical processing phase of Order By clause. Hence 31.01.2019 comes first and 31.12.2018 later.

But when you convert it to varchar(10) to get a required format then actually Order By clause is ordering a Varchar and not Date. Hence 31/12/2018 comes first and 31/01/2019 comes after it.

To resolve the problem if you want to retain the formatting of datetime/date column in Select but Order By with Date value then simply cast the datetime column back to Date in Order by clause.

Pseudo code as:

select CONVERT(VARCHAR(10),RegistrationDate,103) as RegistrationDate from 
Table_Candidate_Info 
order by cast(RegistrationDate as Date) desc -- cast it back to date

Demo Link here: https://rextester.com/WMLQL78387

Upvotes: 1

Esperento57
Esperento57

Reputation: 17492

Try 

order by CONVERT(VARCHAR(10),RegistrationDate,103) desc

or better if you want really keep order by date (and not text) try this:

select Team, CONVERT(VARCHAR(10),RegistrationDate,103) as RegistrationDate, FormFilledAt,CreationBy  
from (
        Select Team, cast(RegistrationDate as date) as RegistrationDate ,FormFilledAt,CreationBy 
        from Table_Candidate_Info 
        where Status='Completed'  
        group by Team,cast(RegistrationDate as date)  ,FormFilledAt,CreationBy
) tmp 
order by RegistrationDate desc

Note: if you want group by date + time remove cast… as date

Upvotes: 1

Fahmi
Fahmi

Reputation: 37493

use distinct and CONVERT(VARCHAR(10),RegistrationDate,103) in order by clause

Select distinct Team,CONVERT(VARCHAR(10),RegistrationDate,103)as RegistrationDate ,FormFilledAt,CreationBy 
from Table_Candidate_Info where Status='Completed'  
order by CONVERT(VARCHAR(10),RegistrationDate,103) desc

Note: You don't need group by since you are not using any aggregated function

Upvotes: 1

Related Questions