Yii ActiveRecord: select only first row with the same value

Question

Let's assume, that I have the following table: (id, subject_id) and in this table I have following records:

id: 1, subject_id: 1
id: 2, subject_id: 1
id: 3, subject_id: 2
id: 4, subject_id: 1
id: 5, subject_id: 2

How can I get only first row where subject_id = 1 and first row where subject_id = 2:

id: 1, subject_id: 1
id: 3, subject_id: 2

score 0 · Answer 1 · answered Apr 18 '14 at 05:22

0

select * from (select id,subject_id FROM table group by subject_id)tempalias where subject_id=1

select * from (select id,subject_id FROM table group by subject_id)tempalias where subject_id=2

answered Apr 18 '14 at 05:22

Ronak Shah

1,539
2
13
20

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. – Michael Roland Apr 18 '14 at 05:48
I think this is what he looking for.. He is looking for the query to achieve the given result and I posted the query.. what else? DO you mean I need to give him link for learn sub query? @Michael Roland – Ronak Shah Apr 18 '14 at 06:28
I flagged this for two reasons: (1) The answer is very poor quality without any explainations; (2) OP specifically asked for Yii ActiveRecord, so I would have expected an answer explaining how OP could do this using that abstraction and not just two plain SQL queries. – Michael Roland Apr 18 '14 at 09:43

score 0 · Accepted Answer · answered Apr 18 '14 at 06:51

0

When you are using group by only first records will be fetched.

$criteria = new CDbCriteria;
$criteria->group = 'subject_id';
$models = YouModelName::model()->findAll($criteria);

answered Apr 18 '14 at 06:51

Alex

8,055
7
39
61

This is incorrect. The generated SQL will not have an aggregate function and the results would therefore be indeterminate. The result of this answer is totally coincidental and unreliable. Please read my explanation. – nazim Apr 20 '14 at 17:57

score 0 · Answer 3 · edited May 23 '17 at 12:20

The solution I would suggest is to use min(id) as the aggregate function in Criteria [assuming id is autoincremented or incremented manually].

$criteria = new CDbCriteria();
$criteria->select = "min(id), subject_id";
$criteria->group = "subject_id";
$models = ModelName::model()->findAll($criteria);

There is no sql function (assuming MySQL db) to get first rows. Check this Group by functions from latest MySQL documentation.

CDbCriteria's group property relates exactly to the underlying SQL engines group by function. And there is no default aggregate function when an explicit aggregate function is not provided. MySQL says it is illegal to select columns which you don't include in group by clause [That is you haven't put an aggregate function on that column].

MySQL documentation notes that if you select a column [without an aggregate function] that is not included in the group by clause, the results would be indeterminate.

Check this SO question as well: GROUP BY behavior when no aggregate functions are present in the SELECT clause

Yii ActiveRecord: select only first row with the same value

3 Answers3