CODEWITHSUNDEEP
jpajpql
Gondim
Gondim

Reputation: 3048

ORDER BY "CASE column" in JPA

I want to ORDER BY the case statement, is it possible? How can I do it?

SELECT new com.systemname.to.UserDataHolder(u.userId, 
CASE  WHEN (u.place.cityId = :cityId) THEN 1  WHEN (u.place.stateId = :stateId) THEN 2  ELSE 3 END)  
FROM User u 
ORDER BY u.userId DESC

Upvotes: 14

Views: 14054

Answers (2)

James
James

Reputation: 18379

What JPA provider are you using?

Try,

SELECT u.userId, 
(CASE  
    WHEN (u.place.cityId = :cityId) THEN 1
    WHEN (u.place.stateId = :stateId) THEN 2
    ELSE 3 END) as myNum
FROM User u 
ORDER BY u.userId, myNum DESC

or,

SELECT new com.systemname.to.UserDataHolder(u.userId, 
CASE  
    WHEN (u.place.cityId = :cityId) THEN 1
    WHEN (u.place.stateId = :stateId) THEN 2
    ELSE 3 END)
FROM User u 
ORDER BY u.userId, CASE  
    WHEN (u.place.cityId = :cityId) THEN 1
    WHEN (u.place.stateId = :stateId) THEN 2
    ELSE 3 END DESC

Upvotes: 19

digitaljoel
digitaljoel

Reputation: 26574

Not in a position to test right now so I'm not sure if this is valid syntax, but could you add an "AS" to it?

SELECT new com.systemname.to.UserDataHolder(u.userId, 
CASE  
    WHEN (u.place.cityId = :cityId) THEN 1
    WHEN (u.place.stateId = :stateId) THEN 2
    ELSE 3 END as myNum)
FROM User u 
ORDER BY u.userId, myNum DESC

Upvotes: 0

Related Questions