Get one result that starts with the same lchar

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
zolee1
Forum Newbie
Posts: 7
Joined: Wed Oct 20, 2004 12:02 am
Location: Far FarAway

Get one result that starts with the same lchar

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

untested

Code: Select all

SELECT *
FROM wordlist
GROUP BY SUBSTRING(word, 1, 1)
ORDER BY word DESC
zolee1
Forum Newbie
Posts: 7
Joined: Wed Oct 20, 2004 12:02 am
Location: Far FarAway

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

ooops, do an ORDER BY word ASC then ;)

you need to lookup 2 things:
- SQL GROUP BY clause
- MySQL SUBSTRING function
zolee1
Forum Newbie
Posts: 7
Joined: Wed Oct 20, 2004 12:02 am
Location: Far FarAway

Post by zolee1 »

Thanks, Timvw!
Post Reply