Have Geek Brain

JPQL / SQL: how to select * from a table with a group of one column?

I would like to select each column of the table, but I want to have different values ​​for one attribute of my rows (City in the example). I don't want extra columns like counters or whatever, just a limited number of results and it seems impossible to get direct LIMIT results in a JPQL query.

Source table:

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

The result is required if I make a report in City:

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

What's the best way to do this? Thank you for your help.

+2


a source to share


2 answers


In JPQL, you can 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]
+4


a source


Don't know about JPQL, but SQL:



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
0


a source






More articles:


All Articles