CODEWITHSUNDEEP
mysqlsqlhibernatejpa
Joffrey
Joffrey

Reputation: 93

How to select max value from a query?

I'm trying to create a query who take the maximum value from a jpa query

first i have:

select count(se.idSecteur) from Site si left join Secteur se on si.idSite = se.idSite group by si.idSite

This return : 

19
22
22
16
26
27
15
24
29
17
13
32
...

I would like to create a query for have only the max value (32 here)

I tried something like :

Select max(select count(se.idSecteur) from Site si left join Secteur se on si.idSite = se.idSite group by si.idSite) ...

But it didn't work

Any advice is appreciated !

Upvotes: 1

Views: 479

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

a simple way for mysql is based or first rows order by desc

  select count(se.idSecteur) 
  from Site si 
  left join Secteur se on si.idSite = se.idSite 
  group by si.idSite
  order by  count(se.idSecteur)  DESC 
  LIMIT 1

or you could use your result as subquery for max

  select max(my_value)
  from (
    select count(se.idSecteur) my_value 
    from Site si 
    left join Secteur se on si.idSite = se.idSite 
    group by si.idSite
  ) t

Upvotes: 2

Related Questions