Update Multiple Rows Mysql from php -- Slight Twist

Question

Hi all,
I asked a similar question yesterday, but now I have a slight twist. Since I already gave credit to an answer, I've decided to create a new question.

Here's what I'm trying to do: Select an arbitrary number of rows, but placing a limit on the number of selected rows of one field.

$query = "SELECT test_id, field1, field2 FROM tablename  WHERE field1 = 'string' LIMIT 5"

So here's the problem: I want a max of one result from each 'test_id', so if a row is selected with test_id 1, then it will skip over any other possible results with test_id 1.

Any ideas?

EDIT: Would GROUP BY test_id LIMIT 5 ... limit the total number of results, or those with the same test_id. I'd like to limit to each test_id to 1, with a total of no more than 5.

Thanks,
Michael

Answer 1

If you use GROUP BY test_id, MySQL will take one of the rows to fetch the values.

Doesn't work with all RDBMS (SQL server for instance), because others require aggregation function on the SELECTed fields that aren't in the GROUP BY clause.