[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] DISTINCT with ORDER BY issue
any ideas on this one??

is this the intended behavior of the ORDER BY? or should I be submitting a bug report?


Derek Knapp


Derek Knapp wrote:

public List<Contacts> recentTouches(Subuser subuser)

{

ÂÂÂ Query query = em.createQuery("SELECT DISTINCT h.contact FROM History h WHERE h.subuser = :subuser ORDER BY h.id DESC");

ÂÂÂ query.setParameter("subuser", subuser);

ÂÂÂ query.setMaxResults(10);

ÂÂÂ return query.getResultList();

}

Â

Generates the following sql

Â

SELECT DISTINCT t0.contactid, t0.fullname, t0.firstname, t0.lastname, t0.salutation, t0.title, t0.email, t0.mailingaddressline1, t0.mailingaddressline2, t0.mailingaddressline3, t0.city, t0.province, t0.postalcode, t0.country, t0.company, t0.businessphone, t0.businessfax, t0.mobilephone, t0.homephone, t0.businessphoneext, t0.smsphone, t0.leadsource, t0.birthdate, t0.emailoptout, t0.create_date, t0.last_modified, t1.id FROM history t1 LEFT OUTER JOIN contacts t0 ON (t0.contactid = t1.contactid) WHERE (t1.subuserid = ?) ORDER BY t1.id DESC

Â

Â

For whatever reason, it has added in t1.id in to the list of items selectedâ. So the number of results from the above function varies depending if there are any duplicate contacts in the top 10 results returned from the queryâ why is it adding t1.id to the select list? I donât care about its valueâ.

Â

Â

Derek Knapp

Â



__________ Information from ESET NOD32 Antivirus, version of virus signature database 4378 (20090828) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users