1

I want to convert following native sql query to hql query. I gone through hibernate docs they given examples on queries with distinct objects/fields but no where given about "distinct on" usage. Can some one help me to resolve this.

my sql query is : 

 select distinct on(mr.id) mr.* 
 from rebate.mr_rebates mr 
 where mr.mr_start_date <= now()
 order by mr.id, mr.mr_start_date desc

Thanks.

a_horse_with_no_name
  • 440,273
  • 77
  • 685
  • 758
VamsiKrishna
  • 197
  • 1
  • 2
  • 9

2 Answers2

0

As far as I know it's as simple as mentioned here. You can find more information at this page.

Community
  • 1
  • 1
evandongen
  • 1,867
  • 16
  • 18
  • Hi evandongen, Thanks for your reply. Actually the examples you given were for getting distinct objects bu here the problem is getting objects based on a distinct field. – VamsiKrishna Oct 20 '11 at 07:51
  • The first link was about a distinct column, but perhaps that wasn't what you were looking for. – evandongen Oct 20 '11 at 08:01
0

Well distinct on seems to be a database specific extension and thus is not standard SQL. Because of this, HQL can't provide an exact equivalent (how would you automatically translate that on databases that don't support that feature?).

So, your best bet might be to change your query to use standard SQL syntax only. For some hints have a look here:

http://www.postgresonline.com/journal/archives/4-Using-Distinct-ON-to-return-newest-order-for-each-customer.html

Thomas
  • 80,843
  • 12
  • 111
  • 143
  • Hi Thomas, Thanks for your reply. Yes i am proceeding to use hibernate native sql to solve this problem. But I just want to know how much performance degrades if I use native sql instead of hql in hibernate. – VamsiKrishna Oct 20 '11 at 08:22