[SOLVED] Return results beginning with first initial

[sphinx9999]

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?

[revraz]

Use LIKE

 

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

[sphinx9999]

@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

[revraz]

You can use WHERE fieldname LIKE b%  if you just want what starts with b, or you can replace b with a variable.

[sphinx9999]

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.

[fenway]

Why LOWER()? That's bad for index usage!

[sphinx9999]

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 ???

[fenway]

Unless your collation is case-sensitive (not the default), this won't do anything at all.

[sphinx9999]

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