0

I have written JPA query in that I want to apply distinct to only one column but here it is applying to all columns in table.

CriteriaBuilder cb = entityManager_gbl.getCriteriaBuilder();
CriteriaQuery<sourceTracking> cq = cb.createQuery(sourceTracking.class);
Root<sourceTracking> data1 = cq.from(sourceTracking.class);
Join<sourceTracking,status> joinobj=data1.join("sts");  
Subquery<Number> subq=cq.subquery(Number.class);
Root<sourceTracking> sbf=subq.from(sourceTracking.class);
subq.select(cb.min(sbf.<Number>get("hopcount"))).groupBy(sbf.<String>get("message_id"));
cq.multiselect(cq.from(sourceTracking.class).get("message_id"));
cq.distinct(true);
cq.select(data1).orderBy(cb.asc(data1.get("message_id")));;
TypedQuery<sourceTracking> tquery = entityManager_gbl.createQuery(cq);

Here is my Hibernate query

 select distinct sourcetrac0_.tablekey as tablekey1_2_, sourcetrac0_.alt_recipient_addr as alt_reci2_2_, 
 sourcetrac0_.alt_recipient_tried as alt_reci3_2_,sourcetrac0_.arrival_time as arrival_4_2_, sourcetrac0_.delivery_status as delivery5_2_, sourcetrac0_.dispatch_time as dispatch6_2_,
 sourcetrac0_.hopcount as hopcount7_2_, sourcetrac0_.id as id8_2_, sourcetrac0_.id1 as id9_2_, sourcetrac0_.mail_orig_time as mail_or10_2_,
 sourcetrac0_.mail_server as mail_se11_2_, sourcetrac0_.mailtype as mailtyp12_2_, sourcetrac0_.message_id as message13_2_, sourcetrac0_.nexthop as nexthop14_2_, 
 sourcetrac0_.precedence as precede15_2_, sourcetrac0_.receiver as receive16_2_, sourcetrac0_.securityclassification as securit17_2_, sourcetrac0_.sender as sender18_2_, 
 sourcetrac0_.subject as subject19_2_ from source_tracking sourcetrac0_ inner join status_table status2_ on sourcetrac0_.message_id=status2_.message_id 
 cross join source_tracking sourcetrac1_ order by sourcetrac0_.message_id asc,sourcetrac0_.hopcount asc.

I want to apply distinct to only one column here is what needed query

select distinct on(sourcetrac0_.message_id) sourcetrac0_.message_id,sourcetrac0_.tablekey as tablekey1_2_, sourcetrac0_.alt_recipient_addr as alt_reci2_2_, 
 sourcetrac0_.alt_recipient_tried as alt_reci3_2_,sourcetrac0_.arrival_time as arrival_4_2_, sourcetrac0_.delivery_status as delivery5_2_, sourcetrac0_.dispatch_time as dispatch6_2_,
 sourcetrac0_.hopcount as hopcount7_2_, sourcetrac0_.id as id8_2_, sourcetrac0_.id1 as id9_2_, sourcetrac0_.mail_orig_time as mail_or10_2_,
 sourcetrac0_.mail_server as mail_se11_2_, sourcetrac0_.mailtype as mailtyp12_2_, sourcetrac0_.message_id as message13_2_, sourcetrac0_.nexthop as nexthop14_2_, 
 sourcetrac0_.precedence as precede15_2_, sourcetrac0_.receiver as receive16_2_, sourcetrac0_.securityclassification as securit17_2_, sourcetrac0_.sender as sender18_2_, 
 sourcetrac0_.subject as subject19_2_ from source_tracking sourcetrac0_ inner join status_table status2_ on sourcetrac0_.message_id=status2_.message_id 
 cross join source_tracking sourcetrac1_ order by sourcetrac0_.message_id asc,sourcetrac0_.hopcount asc
sp21 p
  • 63
  • 1
  • 3
  • 7

1 Answers1

0

That's no possible because DISTINCT is done on the result of the query. Read more here:

DISTINCT for only one Column

Simon Martinelli
  • 34,053
  • 5
  • 48
  • 82