3

I have a table of Sessions. Each session has "session_start_time" and "session_end_time". While the session is open, the end time is empty. I want to fetch the list of sessions, and order it by the following logic:

  1. If the session is open (no end time), order by the start time.
  2. If the session is closed, order by the end time.

Something like:

 ORDER BY (session_end_time == null) ? session_start_time : session_end_time

I'm using JPA and JPQL for the queries, and using Hibernate for the execution. What would you recommend?

NOTE: I would rather not add CASE to the SELECT, but instead keep it clean so I get a list of sessions without additional unneeded fields.

Eldad Mor
  • 5,405
  • 3
  • 34
  • 46

2 Answers2

1

Does

ORDER BY CASE 
    WHEN session_end_time IS NULL THEN session_start_time 
    ELSE session_end_time 
END

work in your DBMS? That doesn't add a field.

Otherwise you can calculate it inside a nested query, but don't include it in the final SELECT clause:

SELECT field1, field2 FROM (
    SELECT field1, field2, CASE WHEN session_end_time... END AS dummyfield
) Q
ORDER BY Q.dummyfield
littlegreen
  • 7,290
  • 9
  • 45
  • 51
  • The CASE does work on PostgreSQL. Seems like your other option adds an additional column to the result set, which I like to avoid. In comparison with COALESCE, the CASE performs slower though (1.7ms for no ordering, 5.61 for coalesce, 6.89 for case), so I may go with Coalesce. – Eldad Mor Nov 17 '10 at 12:47
  • The other option doesn't add another column because the computed column is not mentioned in the `SELECT` clause. `COALESCE` is fine to use instead of `CASE` in this problem, I just didn't think of it... – littlegreen Nov 17 '10 at 13:03
  • Oh oh right, I see it now! That's another interesting approach, however I can't seem to be able to do it along with selecting "*", which is what I want to do in JPQL. – Eldad Mor Nov 17 '10 at 13:10
1

ORDER BY nvl(session_end_time, session_start_time)

nvl is an Oracle function. I'm sure ther are functions like that for other DBMS

Found this one for hibernate: nvl in HIBERNATE: How to simulate NVL in HQL

ORDER BY: https://forum.hibernate.org/viewtopic.php?f=25&t=997220&start=0

Community
  • 1
  • 1
Stahlkocher
  • 219
  • 4
  • 10
  • This helps, thanks! Coalesce does work, and is faster than using CASE. I need to verify that it works in JPQL as well (tested on SQL so far). – Eldad Mor Nov 17 '10 at 12:48