ORDER "CASE Column" in JPA

Question

I want to ORDER in the case, is this possible? How can i do this?

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 

Answer 1

Which JPA provider are you using?

Give it a 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

Answer 2

Unable to check right now, so I'm not sure if this is a valid syntax, but could you add "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