Using the latest version of hibernate and mysql is the following query possible?

Table:
Name
------
Id integer auto_increment
First varchar(100)
Middle varchar(100)
Last varchar(100)

Query:
I need to return a list of all distinct names sorted by last name and the first + middle names. Sounds easy, something like this:
SELECT DISTINCT (name.Last, name.First||' '||name.Middle) FROM Name name ORDER BY name.Last, name.First, name.Middle

Actually I don't know if DISTINCT would work that way, but the problem anyway above is that a first name of "Scott" and a middle name of "Mueller" would come before a first name of "Scott Mueller" and a blank or null (both are allowed, blank means it's known to be empty and null means it's unknown) middle name. Those 2 names (given a common last name) should be equal and only returned once. Not only that, but if the second case was "Scott Mueller2" instead, then it should come IMMEDIATELY after the first case. "Scott Allen" should not come in between in the result.

Is this possible?

Why don't you just give it a try. I believe the order by clause will translate to the same order the database puts it in.

From the hql documentation

http://www.hibernate.org/hib_docs/refer ... ering.html

Just try it that would be the fastest way to find out!

Well it logically doesn't make sense. Yes I can order by last name, then first and then middle. But that will place first name "Scott Allen" AFTER first name "Scott" and middle name "Zebra" (given a common last name). It should be the other way around. Even worse, the first case above will not match "Scott" and "Allen", they'll be considered different names.

I'm able to work around this using HQL case statements, but would love a more elegant solution.

Did you look into comparator's

Using a comparator
http://parasjain01.wordpress.com/2008/1 ... omparator/

or comparator with annotations
http://www.hibernate.org/hib_docs/annot ... /Sort.html

through mappings
http://www.hibernate.org/hib_docs/refer ... ons-sorted