How to group similar records based on their content?

[jasongr]

Hi people

I have a table that holds logging information. It contains 3 columns:
Message_ID: The ID of the message (primary key)
Message_Datetime: The datetime when the message was posted
Message: The actual textual message (varchar(255))

I would like to know what is the distribution of the various messages. What I want to perform is a query that will return the number of messages of each type, where the type is defined by the first 10 characters of the message.

What I mean is that all the messages that begin with the same first 10 characters should be grouped together.

I want to have a query that will display for each group, the number of entries. This way I will be able to sort the results and will know what is the most common message group.

Can anyone show me how this can be done?

thanks

[drallab]

Try:

select count(message_id) number, substring(message, 0, 10) message
from TABLENAME group by substring(message, 0, 10)

This should return 2 columns; one with the number of records for the message and the abbreviated message:

number message
------- --------
2 testing th
4 how do i f
1 1234567890
1 blah blah b
5 etc etc et

This is just a theory since I didn't test it, but I am 99% sure it will work. :)