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? Link to comment https://forums.phpfreaks.com/topic/85531-solved-return-results-beginning-with-first-initial/ 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 Link to comment https://forums.phpfreaks.com/topic/85531-solved-return-results-beginning-with-first-initial/#findComment-436481 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 Link to comment https://forums.phpfreaks.com/topic/85531-solved-return-results-beginning-with-first-initial/#findComment-436501 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. Link to comment https://forums.phpfreaks.com/topic/85531-solved-return-results-beginning-with-first-initial/#findComment-436507 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. Link to comment https://forums.phpfreaks.com/topic/85531-solved-return-results-beginning-with-first-initial/#findComment-436520 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! Link to comment https://forums.phpfreaks.com/topic/85531-solved-return-results-beginning-with-first-initial/#findComment-436710 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 ??? Link to comment https://forums.phpfreaks.com/topic/85531-solved-return-results-beginning-with-first-initial/#findComment-438686 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. Link to comment https://forums.phpfreaks.com/topic/85531-solved-return-results-beginning-with-first-initial/#findComment-438769 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 Link to comment https://forums.phpfreaks.com/topic/85531-solved-return-results-beginning-with-first-initial/#findComment-439971 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.