Page 1 of 1

Get one result that starts with the same lchar

Posted: Sat Nov 06, 2004 11:59 am
by zolee1
Hi,

I am using MySQL with php.

I have one table containing an ID and a breed column. I would like to get the result from breed column, but, if the result starts with the same letter then only one will be printed in ascending order.

For example if I have this in my table:

Bernese Mountain Dog
Bobtail
Bull Terrier
English Bulldog
Havanese
Mudi

This would be printed only:

Bernese Mountain Dog
English Bulldog
Havanese
Mudi

I couldn’t figure out how to achieve this. I tried to search for this on the web, but this problem can't be explained in two-three words.

Posted: Sat Nov 06, 2004 1:04 pm
by timvw
untested

Code: Select all

SELECT *
FROM wordlist
GROUP BY SUBSTRING(word, 1, 1)
ORDER BY word DESC

Posted: Sat Nov 06, 2004 1:46 pm
by zolee1
Thanks timvw,

It is almost what I was looking for.

It gives me the list:

Bull Terrier
English Bulldog
Havanese
Mudi

So, from the result that starts with the same letter it actually returns the last.

I couldn't find any documetation for GROUP BY SUBSTRING. Do you know a please where I can read about it?

Thanks.

Posted: Sat Nov 06, 2004 2:49 pm
by timvw
ooops, do an ORDER BY word ASC then ;)

you need to lookup 2 things:
- SQL GROUP BY clause
- MySQL SUBSTRING function

Posted: Sat Nov 06, 2004 5:57 pm
by zolee1
Thanks, Timvw!