Hi,

I am looking for a way in which to find the most commonly occurring record in my database. For example, if the contents of a simple db were...

apple
banana
apple
apple
melon
melon
lemon

...the result would be apple. I've managed to achieve the desired affect using a loop, working out the count of each entry. This however feels an extremely uneconomical way of doing things!

Is there any better way of doing this? Any help would be very greatly appreciated. :)

Hi,

I am looking for a way in which to find the most commonly occurring record in my database. For example, if the contents of a simple db were...

apple
banana
apple
apple
melon
melon
lemon

...the result would be apple. I've managed to achieve the desired affect using a loop, working out the count of each entry. This however feels an extremely uneconomical way of doing things!

Is there any better way of doing this? Any help would be very greatly appreciated. :)

See the COUNT option of select

I don't have a database system to hand right now to check, but I think you need:

SELECT column_id, count(column_id) AS total
FROM table_name
ORDER BY total DESC;

SELECT column_id, count(column_id) AS total
FROM table_name
ORDER BY total DESC;

This would not work, this would be the output

banana 7
apple 7
melon 7 etc.

This will work:

SELECT fruit, COUNT(fruit) as total FROM table_name GROUP BY fruit ORDER BY total DESC;

apple 3
melon 2
banana 1
lemon 1

This would not work, this would be the output

banana 7
apple 7
melon 7 etc.

This will work:

SELECT fruit, COUNT(fruit) as total FROM table_name GROUP BY fruit ORDER BY total DESC;

apple 3
melon 2
banana 1
lemon 1

Thanks for the correction.

Actually, there could be a place on the forum for useful little sections of SQL like that.

Maybe a mysql sub-section? That would be great!