Re: formulate nested select

On 31 Mar 2009 at 18:15, PJ wrote: <snip> > >> But I see that I may be trying to do too much - I thought of showing how > >> many books were listed under each letter of the alphabet but I don't see > >> how it can be done in any simiple way as it would mean that I would have > >> to do the select once with the ORDER BY and a second time without it > >> just to get the number of listing. If there are a lot of books, like > >> thousands, it might slow down things. > >> I suppose I could live with ORDER BY "title" as that does not require > >> another effort. > >> Any thoughts or suggestions? Hi, Sounds like you need to use the GROUP BY functions of MySQL This SQL is probably wrong because I don't remember seeing your schema (and am too busy here to go looking!) SELECT LEFT(last_name, 1 ) as Letter, Count(bookID) as NumberOfBooks FROM books INNER JOIN <tables that join them...> GROUP BY Letter ORDER BY Letter ASC You will have to play around with that to get the right results. But it should give you something like: Letter,NumberOfBooks A,47 B,21 C,8 ... The MySQL manual has more info: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions-and-modifiers.html Regards Ian --