Selecting Rows With A Unique Value

[Guest FirefoxRocks]

Suppose I had a table like this:

+--------+--------+---------+------+------+-------+| name   | owner  | species |######| age  | death |+--------+--------+---------+------+------+-------+| Fluffy | Harold | cat	 | f	| 2	| NULL  || Buffy  | Harold | hamster | m	| 4	| NULL  || Dizzy  | Harold | hamster | f	| 4	| NULL  || Buffy  | Harold | dog	 | m	| 3	| NULL  |+--------+--------+---------+------+------+-------+

Now I only want to select only 1 row for each species, so in this case I would select the first two rows and the last row. How would I go about this? I cannot filter out name as I am assuming I don't know them. Plus, there's two rows with the same name.The actual database will be much larger than this, and the search query is based on what the user enters. Also there would be more than two rows with the same field value (in this case the same species).A more advanced thing would be to take the average age of all the animals of the same species (I know it doesn't sound realistic in this example), but I would like to get the first part done at least.

[Synook]

You can use SELECT DISTINCT:

SELECT DISTINCT species, * FROM table

[Guest FirefoxRocks]

That's awesome now how do I go about the more advanced thing?

[Synook]

Hmm, you could try AVG() in conjunction with GROUP BY:

SELECT species, AVG(age) FROM table GROUP BY species

[Guest FirefoxRocks]

Turns out I need to use both, now how do I use the LIKE operator in order to create a search function for the database?

[Synook]

LIKE performs a text match, but you are allowed to use wildcards. So, if you wanted to match all species that started with the search term, you could do:

SELECT * FROM table WHERE species LIKE "{term}%"

[Guest FirefoxRocks]

The pattern I'm looking for should match if it is the exact value, or if the field contains that word. This is because in the actual database the field contains more than 1 word.When I type in "mile", I want it to return all of these:Mile long - starts withThree miles - ends withMile - exact match1 mile to go - containsBut I do not want it to match "smile", "miles" or "smiles".I tried this but it is not working:

SELECT * FROM `storms` WHERE `title` LIKE ' mile%' or `title` LIKE '%mile ' or `title`='mile' && `location` BETWEEN 0 AND 4 && `date` BETWEEN 0 AND 4 && `language` BETWEEN 0 AND 4 && `severity`<=14 GROUP BY `id` ORDER BY `date` DESC LIMIT 0, 15;

[Synook]

You probably want a condition set that looks something like the following code, with spaces used to indicate word boundaries.

SELECT * FROM table WHERE title LIKE "{term} %" OR title LIKE "% {term}" OR title = "{term}" OR title LIKE "% {term} %"

[Guest FirefoxRocks]

I put the spaces in the wrong places in my above statement.