Jump to content

Selecting Rows With A Unique Value


Guest FirefoxRocks
 Share

Recommended Posts

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.

Edited by FirefoxRocks
Link to comment
Share on other sites

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

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

Link to comment
Share on other sites

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?

Edited by FirefoxRocks
Link to comment
Share on other sites

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}%"

Link to comment
Share on other sites

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;

Link to comment
Share on other sites

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} %"

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...