Ranking records with how many times they exist

[sukatoa]

For example:

Cat
Dog
Cow
Rat
Cat
Cat
Rat
Cow
Cow
Cow

(RESULT)
Where it exist:
Cow 4 times
Cat 3 times
Rat 2 times
Dog 1 time

I would like to have a result like the one i mentioned above (RESULT)

How would it be queried like?

The current implementation has 2 queries, 1st is to select them distinctly, 2nd is one by one, each of the unique result will select again the db to count how many times it exist, i think it is a bad implementation and i hope it is possible to query once in the DB Server, any ideas?

[mwasif]

This can be achieved by COUNT() and GROUP BY.

[code=mysql]SELECT column_name, COUNT(*) as occurence FROM table_name
GROUP BY column_name
ORDER BY occurence DESC[/code]

[nbiswas]

Solution to Ranking records with how many times they exist

Try this

select Result = animals + '  ' +  CAST(COUNT(animals) as varchar(10))  + '  times'
from AnimalTable
group by animals 
order by COUNT(animals) desc

Output:

Result
Cow  4  times
Cat  3  times
Rat  2  times
Dog  1  times

[sukatoa]

Thank you for your replies experts. Effective =)