sphinx9999 Posted January 11, 2008 Share Posted January 11, 2008 Hi, I need to write a query to return all results beginning with a certain initial character and this character should be alphabetically (or numerically) the first in all results (...not very clear, soz). For example, I have a database with the following rows: banana, bonzai, butter, cat, cough, dog, doughnut I would want the query to return 'banana', 'bonzai', 'butter' ('b' is the first initial in the database). As a starter, so far I have got: SELECT word FROM tablename WHERE... Impressed? Quote Link to comment Share on other sites More sharing options...
revraz Posted January 11, 2008 Share Posted January 11, 2008 Use LIKE http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html Quote Link to comment Share on other sites More sharing options...
sphinx9999 Posted January 11, 2008 Author Share Posted January 11, 2008 @revraz: thanks for your help, it got me thinking and I then came up with the following: SELECT word FROM tablename WHERE LEFT(LOWER(word),1) = (SELECT LEFT(LOWER(word),1) FROM tablename ORDER BY word LIMIT 1) which works nicely. I'm pretty confident that there must be a more efficient query than this though, so if anyone fancies a go... Ta Quote Link to comment Share on other sites More sharing options...
revraz Posted January 11, 2008 Share Posted January 11, 2008 You can use WHERE fieldname LIKE b% if you just want what starts with b, or you can replace b with a variable. Quote Link to comment Share on other sites More sharing options...
sphinx9999 Posted January 11, 2008 Author Share Posted January 11, 2008 You can use WHERE fieldname LIKE b% if you just want what starts with b, or you can replace b with a variable. The problem is I don't know what the first initial ('b' in this case) is going to be. The subquery SELECT LEFT(LOWER(word),1) FROM tablename ORDER BY word LIMIT 1 works that out but I could not figure out a way of using a LIKE comparison in the main query: SELECT word FROM tablename WHERE LOWER(word) LIKE (SELECT LEFT(LOWER(word),1) FROM tablename ORDER BY word LIMIT 1) This then doesn't let me use the '%' extender. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 11, 2008 Share Posted January 11, 2008 Why LOWER()? That's bad for index usage! Quote Link to comment Share on other sites More sharing options...
sphinx9999 Posted January 14, 2008 Author Share Posted January 14, 2008 Why LOWER()? That's bad for index usage! The lower is just in place to ensure the correct case. The field `word` is not indexed. Could you explain why you say it is bad for index usage - I must be misunderstanding something ??? Quote Link to comment Share on other sites More sharing options...
fenway Posted January 14, 2008 Share Posted January 14, 2008 Unless your collation is case-sensitive (not the default), this won't do anything at all. Quote Link to comment Share on other sites More sharing options...
sphinx9999 Posted January 15, 2008 Author Share Posted January 15, 2008 Unless your collation is case-sensitive (not the default), this won't do anything at all. yep, good point. I see what you mean now. Thanks Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.