0

Im struggling with this and tried different groupby options with no success. This is my table

id  date         advertiserID    providerID    companyID
1   2022-03-01   3               9             3
2   2022-08-01   3               9             3
3   2022-02-01   700             52            3
4   2022-12-01   456             15            1
5   2022-11-01   3               9             2

And im trying to select max date for each record with the same advertiser and provider so that, as per above table, it would return:

id  date         advertiserID    providerID    companyID
3   2022-02-01   700             52            3
4   2022-12-01   456             15            1
5   2022-11-01   3               9             2 <--- this is the max date for advid 3 and provid 9

Tried with this but it returns MAX date with wrong id and companyID:

$currentAssoc = CompaniesAdvertisersProvidersAssoc::find()
    ->select("MAX(date) as date, companiesAdvertisersProviders.id as bmAssocID, advertiserID, providerID, companyID, advertisers.name as advertiser, providers.name as provider, companies.id as companyID, companies.name as company, advertisers.image as advertiserImage, providers.image as providerImage, companies.companyLogo as bmImage")
    ->joinWith(['advertiser', 'provider', 'company'], false)
    ->groupBy(['advertiserID', 'providerID'])
    ->orderBy([
        'company'       => SORT_ASC,
        'advertiser'    => SORT_ASC
        ]
    )
    ->asArray()
    ->all();

id  date         advertiserID    providerID    companyID
4   2022-12-01   456             15            1
1   2022-11-01   3               9             3 <--- id should be 5 and companyid 2
3   2022-02-01   700             52            3

Thank you in advance,

farrusete
  • 435
  • 9
  • 24
  • Does this answer your question? [Yii2 - Getting unknown property: yii\console\Application::user](https://stackoverflow.com/questions/34174750/yii2-getting-unknown-property-yii-console-applicationuser) – Muhammad Omer Aslam Apr 19 '22 at 19:16
  • I cant see a mysql related question nor answer there. Maybe the link is wrong? Thanks – farrusete Apr 20 '22 at 09:45

1 Answers1

1

if you want to see also companyID, it should be also in grouped by section. Try to test simple SQL query which works and then add joins and other columns. This works:

create table aaa (id int, dd date, aid int, pid int, cid int);
insert into aaa values (1, '2022-03-01', 3 ,9 ,3);
insert into aaa values (2, '2022-08-01', 3 ,9 ,3);
insert into aaa values (3, '2022-02-01', 700 ,52,3);
insert into aaa values (4, '2022-12-01', 456 ,15,1);
insert into aaa values (5, '2022-11-01', 3 ,9 ,2);

# select max(dd), aid, pid, cid from aaa group by aid,pid,cid;
    max     | aid | pid | cid 
------------+-----+-----+-----
 2022-12-01 | 456 |  15 |   1
 2022-02-01 | 700 |  52 |   3
 2022-11-01 |   3 |   9 |   2
 2022-08-01 |   3 |   9 |   3
(4 rows)
MWik
  • 103
  • 5
  • Thank you for your answer but it is showing two records for the same pid and aid: `2022-11-01 | 3 | 9 | 2` and `2022-08-01 | 3 | 9 | 3` <--- this last one has a lower date than above one and should not be returned`. What I would need is all aid and pid with the highest date and its cid (the cid for that highest date). Thanks again – farrusete Apr 20 '22 at 09:03
  • `select min(cid), max(dd) maxdate, aid, pid from aaa group by aid,pid;` – MWik Apr 20 '22 at 09:37
  • Thank you again @MWik. It certainly returns the 3 rows but this is a large table and it could happen to have higher or lower cid depending on the relation. How could I get only the row with max date and its current cid? – farrusete Apr 20 '22 at 09:51
  • define what is current cid... You can use another select to get it if you know already max date. You can even use subselect/ subquery and get it within one select call. – MWik Apr 21 '22 at 10:06
  • Thanks again. cid is Company ID and is a relation with other table – farrusete Apr 27 '22 at 10:03