Count, order desc and select the top 5

Question

SQL Server 2012

We have a table, for example:

ticket, type ------------------ 1234, hardware 1543, software 8859, network 5832, hardware 4900, hardware 8403, software 7859, network 4332, telephone 8721, database 

Our goal is to count all the tickets belonging to each type (so in this case the result should be 3 hardware, 2 software, 2 networks, 1 phone and 1 database ticket), order them in descending order and select the first 5 resulting rows.

We are trying to determine the top 5 "popular" or most well-known types of tickets (and how many are there).

I have a counting part down, but not sure how to proceed with the desc order and choosing the first 5.

Thanks!

Answer 1

In SQL Server, you can use TOP to select a specific number of rows along with order to get the correct entries:

 select top 5 type, count(*) Total from yourtable group by type order by total desc 

See SQL Fiddle with Demo

Answer 2

 select * from ( select type, count(*) from table group by type order by 2 desc ) where rownum <=5