<p>Is there a way to use sql-server like analytic functions in Hibernate?</p>

<p>Something like  </p>

<pre><code>select foo from Foo foo where f.x = max(f.x) over (partition by f.y)
</code></pre>

[{'answer_id': 75287, 'author': 'Jason Weathered', 'author_id': 3736, 'author_profile': 'https://Stackoverflow.com/users/3736', 'pm_score': 3, 'selected': False, 'text': '<p>You are after a native SQL query.</p>\n\n<p>If you are using JPA the syntax is:</p>\n\n<pre><code>Query q = em.createNativeQuery("select foo.* from Foo foo " +\n                               "where f.x = max(f.x) over " +\n                               "(partition by f.y)", Foo.class);\n</code></pre>\n\n<p>If you need to return multiple types, take a look at the <a href="http://java.sun.com/javaee/5/docs/api/javax/persistence/SqlResultSetMapping.html" rel="noreferrer">SQLResultSetMapping</a> annotation.</p>\n\n<p>If you\'re using the the Hibernate API directly:</p>\n\n<pre><code>Query q = session.createSQLQuery("select {foo.*} from Foo foo " +\n                                 "where f.x = max(f.x) over "+\n                                 "(partition by f.y)");\nq.addEntity("foo", Foo.class);\n</code></pre>\n\n<p>See <a href="http://www.hibernate.org/hib_docs/v3/reference/en/html/objectstate.html#objectstate-querying-nativesql" rel="noreferrer">10.4.4. Queries in native SQL</a> in the Hibernate documentation for more details.</p>\n\n<p>In both APIs you can pass in parameters as normal using setParameter. </p>\n'}, {'answer_id': 2027784, 'author': 'Petr Macek', 'author_id': 15045, 'author_profile': 'https://Stackoverflow.com/users/15045', 'pm_score': 2, 'selected': False, 'text': '<p>Another approach would be to use the mapping. Please see this article: <a href="https://forums.hibernate.org/viewtopic.php?f=1&amp;t=998482" rel="nofollow noreferrer">https://forums.hibernate.org/viewtopic.php?f=1&amp;t=998482</a></p>\n\n<p>I am against the usage of native SQL queries in Hibernate... you lose the benefits of having a mapping:-)</p>\n'}, {'answer_id': 33003427, 'author': 'Eric Mayes', 'author_id': 1661071, 'author_profile': 'https://Stackoverflow.com/users/1661071', 'pm_score': 2, 'selected': False, 'text': '<p>Yes you can, but you will need to extend the hibernate dialect like the following:</p>\n\n<p>import org.hibernate.dialect.Oracle10gDialect;</p>\n\n<pre><code>public class ExtendedDialect extends Oracle10gDialect{\n    public ExtendedDialect()\n    {\n           super();\n           registerKeyword("over");\n           registerKeyword("partition");\n    }\n}\n</code></pre>\n\n<p>Once this class is on your classpath, you will need to tell hibernate to use it instead of the original dialect (in this case Oracle10gDialect).  I am not sure which frameworks you are using, but in the case of Spring, you can use the following property under the LocalContainerEntityManagerFactoryBean:</p>\n\n<pre><code>        &lt;property name="jpaVendorAdapter"&gt;\n            &lt;bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"&gt;\n                &lt;property name="databasePlatform" value="path.to.dialect.ExtendedDialect" /&gt;\n            &lt;/bean&gt;\n        &lt;/property&gt;\n</code></pre>\n\n<p>Then you can use over and partition in @Formula annotations, @Where annotations and other hibernate features without confusing hibernate.</p>\n'}]
['https://Stackoverflow.com/questions/74057', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/12905/']