Hi,
I have tried to do a distinct query in Hibernate but it still keeps giving me duplicates.

My query is SELECT distinct ROLENAME, ROLEID FROM A_TABLE

All I want is the ROLENAME to be distinct but it keeps giving me duplicate role names. This is supposed to be a very simple query. I even tried a native SQL query with the same result.

Here's my code :
Query query = session.createQuery(
"SELECT DISTINCT A.roleName, A.secAppRoleID from secAppRole A");

This returns a list of object arrays. The rolename is still NOT distinct.

---------------
Native query attempt -- I tried the native sql query like this without much luck. It still gives me 2 rolenames which are the same.

Query query = session.createSQLQuery(
"SELECT DISTINCT {A}.ROLE_NAME AS {A.roleName}, " +
" {A}.SEC_APP_ROLE_ID AS {A.secAppRoleID} from SEC_APP_ROLE {A} ", "A", SecAppRole.class );

Could someone pls help me get this simple thing working.

Vinh

Hibernate has the same semantics as SQL. Get a book on SQL and read it.

Collection coll = new ArrayList();
Session sess = HibernateUtilSourceGeneral.currentSession();
iter = sess.iterate( "SELECT DISTINCT o.levelId from Member o where o.levelId IS NOT NULL");

while (iter.hasNext()){
Object row = (Object) iter.next();
coll.add(row);
}

Hopefully this helps
Nathalie