order by nulls first / last

Description

support of nulls first / last in order clause

"NULLS LAST" is part of the SQL 99 standard.

The syntax is as follows:

ORDER BY [COLUMN NAME] [ASC | DESC] [NULLS FIRST | NULLS LAST]

In different DBs, the sorting of nulls relative to other values is handled differently.

PostgreSQL - Nulls are considered HIGHER than non-nulls.
DB2 - Higher
MSSQL - Lower
MySQL - Lower
Oracle - Higher

The following DBs have supported this functionality:
DB2 V7
Oracle 9i

PostgreSQL, MySQL, SQLServer do not appear to support this from what I can gather.

see http://forum.hibernate.org/viewtopic.php?t=942176&start=0&postdays=0&postorder=asc&highlight=

Attachments

1

Activity

Show:

Former user December 16, 2018 at 8:42 PM

I see this had been implemented for the Criteria API which has now been deprecated. Is it likely that we’ll see this feature return in CriteriaBuilder?

Stephane Eybert December 6, 2012 at 11:15 AM

Here is what a minor issue makes me do:

@Override
@SuppressWarnings("unchecked")
public List<NavlinkItem> findWithNavlink(Navlink navlink) {
String statement = "select id, version, name, description, image, image_over as imageOver, url, blank_target as blankTarget, language_code as languageCode from navlink_item where navlink_id = :navlinkId order by language_code nulls first";
Query query = getSession().createSQLQuery(statement)
.addScalar("id", StandardBasicTypes.INTEGER)
.addScalar("version", StandardBasicTypes.INTEGER)
.addScalar("name")
.addScalar("description")
.addScalar("image")
.addScalar("imageOver")
.addScalar("url")
.addScalar("blankTarget", StandardBasicTypes.BOOLEAN)
.addScalar("languageCode");
query.setInteger("navlinkId", navlink.getId());
return query.setResultTransformer(Transformers.aliasToBean(NavlinkItem.class)).list();
}

instead of:

public List<NavlinkItem> findWithNavlink(Navlink navlink) {
Criteria criteria = getSession().createCriteria(getPersistentClass());
criteria.add(Restrictions.eq("navlink", navlink)).addOrder(Order.asc("languageCode").nullsFirst());
return criteria.list();
}

Stephane Eybert November 22, 2012 at 11:10 AM
Edited

It's too bad this issue is still in the way, for all my integration tests developed against a MySql database now fail against Oracle 10g XE.

It would be very helpful to have Hibernate offering something like:
Order.Asc("myproperty").Nulls.First()

Grégory OLIVER September 20, 2012 at 12:01 PM
Edited

I think you should change this priority, it's not a minor changed, since we have 59 votes for this.

Eric Jain May 23, 2011 at 9:05 PM

This would be quite helpful for making applications behave more consistently across databases; I ran into this when I noticed that H2 (which we use for testing) sorts nulls lower, whereas PostgreSQL (which we use in production) sorts nulls higher...

Fixed

Details

Assignee

Reporter

Components

Priority

Created May 10, 2005 at 10:39 PM
Updated December 16, 2018 at 8:42 PM
Resolved January 28, 2013 at 9:17 PM