php select statement using max

Question

So I have 3 rows in a table

╔══════════════════════════════════════════════════════╗
║ Id TenancyId   StartDate    EndDate       Other Data ║
╠══════════════════════════════════════════════════════╣
║ 31 5198        2016-05-02   NULL                     ║
║ 30 5198        2016-01-08   2016-04-07               ║
║ 29 5198        2016-04-08   2016-05-01               ║
╚══════════════════════════════════════════════════════╝

I am using the query :-

SELECT Id, max(StartDate), FrequencyNumber, FrequencyLetter, Arrears FROM PaymentSchedule WHERE TenancyId=5198 GROUP BY TenancyId

I was expecting when I used the max it would return all the details from the row with the maximum date however I am getting this info back in my query...

Array ( [Id] => 30 [max(StartDate)] => 2016-05-02

so it is picking out the maximum start date but returning the wrong row (ID) I have indexed the columns.

Any help would be much appreciated

`max` on a date? shouldn't just that be ordered by date descending? — Kevin, Dec 01 '16 at 01:46

score 0 · Answer 1 · edited May 23 '17 at 12:01

It's not how these functions work. They aggregate only grouped columns and others are selected randomly (Other db engines don't allow this kind of selects at all). You may read this chapter of manual: http://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

The single row with max value can be found with ordinary select with ...ORDER BY StartDate DESC LIMIT 1.

If you wanted multiple aggregates (like for example select "max row" for each group) you need to use subquery or special join (see: this answer)

php select statement using max

1 Answers1