CODEWITHSUNDEEP
sqlsql-server-2005
Snake Eyes
Snake Eyes

Reputation: 16764

Order by condition for two DATETIME columns

I read ORDER BY condition and How to add condition in 'order by'? but these don't answer to my issue.

I have 2 datetime columns called CREATION_DATE and UPDATE_DATE.

I want to sort (using ORDER BY clause) by :

I wrote 

SELECT * FROM Table1
ORDER BY
CASE Table1.CREATION_DATE
    WHEN Table1.CREATION_DATE = Table1.UPDATE_DATE THEN Table1.CREATION_DATE
    ELSE Table1.UPDATE_DATE
END ASC

but it does not work, I get an error at the = symbol.

I tried with other operators... Of course, I make mistake but I don't know if is correct to use operators in WHEN statement.

Is not accepted operators in WHEN ?

Thank you

Upvotes: 1

Views: 2298

Answers (4)

StuartLC
StuartLC

Reputation: 107347

In C++ / java / C# 'analogy', the SQL Case statement can be used as both a switch(), and an if / elseif / if construct.

e.g. both of the following are legal:

declare @flag bit
set @flag = 0
select case 
    when @flag = 1
        then '1 selected'
    else
        '0 selected'
    end

And

declare @flag bit
set @flag = 0
select case @flag
    when 1
        then '1 selected'
    else
        '0 selected'
    end

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24086

SELECT * FROM Table1
ORDER BY
CASE 
    WHEN Table1.CREATION_DATE = Table1.UPDATE_DATE THEN Table1.CREATION_DATE
    ELSE Table1.UPDATE_DATE
END ASC

Upvotes: 0

podiluska
podiluska

Reputation: 51504

Try

ORDER BY UPDATE_DATE, CREATION_DATE

Upvotes: 0

juergen d
juergen d

Reputation: 204884

try

ORDER BY
CASE WHEN Table1.CREATION_DATE = Table1.UPDATE_DATE
     THEN Table1.CREATION_DATE
     ELSE Table1.UPDATE_DATE
END ASC

or simpler 

ORDER BY Table1.UPDATE_DATE ASC

since you want to order by UPDATE_DATE only if it is equal to CREATION_DATE. In that case you could order by UPDATE_DATE too. In other cases you want to oder by UPDATE_DATE. So just only order by that. It is the same.

Upvotes: 6

Related Questions