CODEWITHSUNDEEP
mysql
Dirk
Dirk

Reputation: 6884

Update Multiple Rows Mysql from php -- Slight Twist

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

Upvotes: 0

Views: 491

Answers (1)

instanceof me
instanceof me

Reputation: 39138

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.

Upvotes: 1

Related Questions