Grouping alphabetically by first letter

[Web_Mountaineer]

I want to create a query that will group results based on the first letter of the record text. First they would be grouped by Category, then by letter of the alphabet and then be Ordered alphabetically. I would also like to use this query to try and populate a CFTREE.

As an example I would use Books.

The query would Order by: Book Category, the first letter of the Book Title, and finally the Book Title.

The results would be Grouped by: Book Category, then first letter of the Book Title.

[Dan_Bracuk]

Does your db have a substring function?

[Web_Mountaineer]

Hi Dan,

I am using mySQL and it does have a substring function. It is easy enough retrieve the first letter of a title but I am confused how to go about writing the query to be able to ORDER BY that letter.

[Dan_Bracuk · Accepted solution]

Don't worry about the order by. Just order by the category then the book title. Select the first letter and use it for grouping in your cfoutput.