title: SQL server query to get this output
body: <p>I have a query which i am unable to get the output.
  Below is the query to be executed</p>
  
  <p>Q: 2.   Write a query to find the city Name, Id and Count that has got maximum number of vehicles associated.</p>
  
  <p>Now, Given is the one table on which the query should be executed.</p>
  
  <pre><code>Vehicle_Detail_ID   City_ID
  56                    242
  57                    242
  58                    242
  59                    243
  60                    241
  61                    242
  62                    245
  </code></pre>
  
  <p>Another table which has the city name</p>
  
  <pre><code>City_ID City_Name
  242          Bangalore
  243          ChamarajNager
  241          Bellary
  245          Chitradurga
  </code></pre>
  
  <p>EXPECTED OUTPUT: </p>
  
  <pre><code>City_ID No_Vehicles
  242          4
  </code></pre>
  
  <p>Please tell me how to write the query which fetches me the right output.</p>
  
  <p>Given below is the query which gives the city Id and its vehicle count.
  TRIED: </p>
  
  <pre><code>SELECT c.city_id, COUNT(c.City_ID) AS NO_vehicles
    FROM city c, vehicle_details v 
   WHERE c.City_ID = v.City_ID
   GROUP BY c.City_ID
  </code></pre>
  
  <p>ACTUAL OUTPUT </p>
  
  <pre><code>City_ID No_Vehicles
  242             4
  243             1
  241             1
  245             1
  </code></pre>
  
  <p>Please help me to get max of these as shown in EXPECTED OUTPUT.</p>