StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHibernate: Select N rows, but with only unique values in one column
text
Body
copied!<p>I want to implement <a href="https://stackoverflow.com/questions/190702/mysql-select-n-rows-but-with-only-unique-values-in-one-column">this</a> with Hibernate criteria. </p> <p>Let's say I have data like(from the link):</p> <pre><code>ID Name City Birthyear 1 Egon Spengler New York 1957 2 Mac Taylor New York 1955 3 Sarah Connor Los Angeles 1959 4 Jean-Luc Picard La Barre 2305 5 Ellen Ripley Nostromo 2092 6 James T. Kirk Riverside 2233 7 Henry Jones Chicago 1899 </code></pre> <p>And I want to implement this with Hibernate(from the link):</p> <pre><code>SELECT P.*, COUNT(*) AS ct FROM people P JOIN (SELECT MIN(Birthyear) AS Birthyear FROM people GROUP by City) P2 ON P2.Birthyear = P.Birthyear GROUP BY P.City ORDER BY P.Birthyear ASC LIMIT 10; </code></pre> <p>If I have an entity:</p> <pre><code>@Entity @Table(name = "people") public class People { @Id private int id; @Column private String name; @Column private String city; @Column int birthyear; } </code></pre> <p>Then I can make a criteria without self join part(this might not work):</p> <pre><code>Criteria criteria = sessionFactory.getCurrentSession() .createCriteria(People.class, "people") .setProjection(Projections.projectionList() .add(Projections.property("people.id")) .add(Projections.property("people.name")) .add(Projections.property("people.city")) .add(Projections.groupProperty("people.city))) .addOrder(Order.asc("people.birthyear")); </code></pre> <p>How to implement self join part?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload