K1000 - Service Desk Category Top 10
I'm trying to automate some reporting and I'm looking to get a Top 10 of categories for the month. If I try to use a SELECT TOP 10 command I get an error. Here's my script without the TOP 10 command and this spits out an unsorted count of tickets. How can I get it to just give me the TOP 10 calls?
HD_CATEGORY.NAME AS Category,
count(HD_TICKET.TITLE) as Count_Of_Tickets
FROM HD_TICKET, HD_CATEGORY
HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID
(HD_TICKET.HD_QUEUE_ID = 1)
(( date(HD_TICKET.TIME_CLOSED) >= date_sub(curdate(), interval dayofmonth(curdate())-1 day) and month(HD_TICKET.TIME_CLOSED) = month(curdate())))
group by CATEGORY
Answer Chosen by the Author
The "TOP 10" syntax is specific to Oracle and does not work in MySQL. At the bottom of your query (after the "group by CATEGORY" line), add the following.
First, to order the tickets from highest count to lowest:
order by Count_Of_Tickets desc
Second, to get only the first 10 records returned: