how to write sql command to get data like this

What is the sql command to get top 10 model which highest available in car table on mysql database?

there are 2 tables related, model and car .Not mension about brand table yet for simplicity. display like this below.

> model                      car
> --------------------       -开发者_如何学Go------------------------------------
> model_id| model_name|     |car_id|license_plate_number|model_id  |   
> ---------------------      -------------------------------------
>     0   | altis     |     |   0   |  xd-3203          |     1    |
>     1   | march     |     |   1   |  dj-2323          |     1    |
>     2   | civic     |     |   2   |  kk-9999          |     2    |

model_id is foreign key in car table.

assume there are are 50 models available and 200 cars. please guild me how to get top10 model which highest in car table. and get available car number for each top10 model.

or tell me the concept , do i need group by , count or something else?

Your basic query to get the top 10 based on counting rows is:

-- SQL SERVER
select top 10 model_id
     , count(*) as qtyCars
  from cars
 group by model_id
 order by count(*) desc

-- Other servers
select model_id
     , count(*) as qtyCars
  from cars
 group by model_id
 order by count(*) desc
 LIMIT 10

EDIT: I don't normally push my own blog, but this post I wrote a few years ago consistently is my most popular going by search hits, and covers GROUP BY: http://database-programmer.blogspot.com/2008/04/group-by-having-sum-avg-and-count.html

Not sure how you're wanting to group them, so I just grouped them by their model_id

SELECT TOP 10 * FROM model JOIN car ON car.model_id = model.model_id GROUP BY car.model_id DESC