How to return the size of the greatest Collection in JPA?

Question

Here's my code:

@Entity
@Table("Books")
public class BookImp implements Book {

@Id
@GeneratedValue
@Column(name = "id")
private Long id;

@ElementCollection
private Set<String> authors = new HashSet<String>();

// getters + constructors

}

I want to create an SQL query which will return the size of the greatest authors collection. How can I do this?

EDIT: To those who suggest using a for loop, this is how I want the answer to look like:

@Override
public int getLargestAuthors() {
return entityManager.createQuery("select b.authors from BookImpl b where b.authors.size ...").getSingleResult().size();
}

Answer 1

Not a mysql wizard but can't you do it like this?

select MAX(b.authors.size) from BookImpl b

Answer 2

SELECT m FROM BookImp m where size(m.authors) = (select max(size(c.authors)) FROM BookImp c)

This returns one or more BookImp's that have the greatest size.