need help with sql server 2008 query - max from select

Question

hi

i have this query and i need to get the max value of number and the city

how to do it ?

select city,count(id) as number
from men
group by city
order by number desc

thank's in advance

Answer 1

Simple. Add a TOP clause to restrict the number of returned rows to 1. Note that parentheses for the top clause are optional in select statements where the number of rows is a constant. If you use anything other than a constant, you'll need parentheses and SQL Server 2005+. However, a top clause with constant number of rows without parentheses works on 2000 too.

select top 1 city,count(id) as number
from men
group by city
order by number desc

Answer 2

select top(1) city,count(id) as number
from men
group by city
order by number desc

Answer 3

Your query seems ok.

Just add top 1 to get only first result:

select top 1 city,count(id) as number
from men
group by city
order by number desc