JPQL / SQL: How to select * from a table with group by on a single column?

Question

I would like to select every column of a table, but want to have distinct values on a single attribute of my rows (City in the example). I don't want extra columns like counts or anything, just a limited number of results, and it seems like it is not possible to directly LIMIT results in a JPQL query.

Original table:

ID    |   Name   |   City
---------------------------
1     |   John   |   NY
2     |   Maria  |   LA
3     |   John   |   LA
4     |   Albert |   NY

Wanted result, if I do the distinct on the City:

ID    |   Name   |   City
---------------------------
1     |   John   |   NY
2     |   Maria  |   LA

What is the best way to do that? Thank you for your help.

Answer 1

Dunno about JPQL, but the SQL would be:

SELECT x.*
  FROM TABLE x
  JOIN (SELECT MIN(t.id) AS min_id,
               t.city
          FROM TABLE t
      GROUP BY t.city) y ON y.min_id = x.id
                        AND y.city = x.city

Answer 2

In JPQL, you could do something like this:

select e 
from MyEntity e 
where e.id in (select min(e.id) from MyEntity e group by e.city) 

This returns:

MyEntity [id=1, name=John, city=NY]
MyEntity [id=2, name=Maria, city=LA]