14

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
Gondim
  • 3,038
  • 8
  • 44
  • 62
  • Some advertising to change this for the JPA 3.0 spec: https://github.com/eclipse-ee4j/jpa-api/issues/297 – Kawu Jan 05 '21 at 17:57

2 Answers2

19

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
James
  • 17,965
  • 11
  • 91
  • 146
  • The second option works fine, but it takes longer than it should with only one CASE. What I did was, I setted the CASE value in Java, and I've put the CASE WHEN only on ORDER BY clause. It worked perfectly and faster than the old SQL that was on application. – Gondim Nov 01 '11 at 16:25
0

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
digitaljoel
  • 26,265
  • 15
  • 89
  • 115
  • No. At least this is not specified by the JPA (yet?). See https://github.com/eclipse-ee4j/jpa-api/issues/297 – Kawu Jan 05 '21 at 17:52