Question: Let's say I have the following 2 classes:

```
@Entity
class Article {

    @Id
    int id;

    @ManyToMany(mappedBy="articles")
    List<Tag> tags;
}

@Entity
@NamedQueries({
    @NamedQuery(name = "Tag.trends", query = "SELECT t FROM Tag t") // Some magic stuff here..
})
class Tag {

    @Id
    String name;

    @ManyToMany
    List<Articles> articles;
}

```

Now I want to create a named query to select the most used tags.

So this will require a *join*, *count* and an *order by*. But I can't seem to figure this out.

**Question:**
How can I select the most used tags?

For example, if the following articles have the following tags:

```
Article: Tags
0: A, B
1: A
2: C
3: A, C

```

**Output**

```
A // 3 times
C // 2 times
B // 1 time

```

Answer: 
You can achieve what you want using something like

```
SELECT t FROM Tag t ORDER BY SIZE(t.articles) ASC

```

That would bring you the list of tags ordered by number of articles.
Work your way backwards on these kinds of problems. Start with the SQL query:

```
select 
  count(t.name), t.name 
from article a 
  join article_tags ats 
  join tag t 
where 
  a.id=ats.article_id 
  and ats.tag_id = t.id 
group by 
  t.name 
order by 
  count(t.name) 
desc;

```

Then the JPQL should more or less follow from that:

```
List<Tuple> t = em.createQuery("select t.name as name, count(t.name) as count from Article a join a.tags t group by (t.name) order by count(t.name) desc", Tuple.class).getResultList();
t.stream().forEach((tp)-> {
    System.out.println("Count of " + tp.get("name") + " = " + tp.get("count"));
}); 

```

This gives me the following output:

```
Hibernate: select tag2_.name as col_0_0_, count(tag2_.name) as col_1_0_ from Article article0_ inner join Tag_Article tags1_ on article0_.id=tags1_.articles_id inner join Tag tag2_ on tags1_.tags_id=tag2_.id group by tag2_.name order by count(tag2_.name) desc
Count of A = 2
Count of B = 1
Count of C = 1

```

UPDATE: If you want a list of tags, just change the first element of the tuple to be a tag. And of course you can ignore the count if you want:

```
List<Tag> t = em.createQuery("select t from Article a join a.tags t group by (t.name) order by count(t.name) desc", Tag.class).getResultList();
t.stream().forEach((tag)-> {
    System.out.println("Tag = " + tag.getName());
}); 

```

And get the following output:

```
Hibernate: select tag2_.id as id1_1_, tag2_.name as name2_1_ from Article article0_ inner join Tag_Article tags1_ on article0_.id=tags1_.articles_id inner join Tag tag2_ on tags1_.tags_id=tag2_.id group by tag2_.name order by count(tag2_.name) desc
Tag = A
Tag = B
Tag = C

```