Select *, max(date) works in phpMyAdmin but not in my code

Question

OK, my statement executes well in phpMyAdmin, but not how I expect it in my php page.

This is my statement:

SELECT `egid`, `group_name` , `limit`, MAX( `date` )
  FROM employee_groups
 GROUP BY `egid`
 ORDER BY `egid` DESC ;

This is may table:

CREATE TABLE `employee_groups` (
  `egid` int(10) unsigned NOT NULL,
  `date` date NOT NULL,
  `group_name` varchar(50) NOT NULL,
  `limit` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`egid`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

I want to extract the most recent list of groups, e.g. if a group has been changed I want to have only the last change. And I need it as a list (all groups).

Answer 1

Your query might be broken. You should not select fields that aren't in the group by unless one of the following two conditions apply:

• You use an aggregate function.
• The value is functionally dependant on the grouped by columns.

The two fields group_name and limit appear to break these rules. This means that you will get indeterminate results for these columns.

If you are trying to select the max per group then you should use a slightly different technique. See Quassnoi's article MYSQL: Selecting records holding a groupwise maximum for a variety of methods you could use.

Here's one way to do it:

SELECT  di.*
FROM    (
        SELECT   egid, MAX(date) AS date
        FROM     employee_groups d
        GROUP BY egid
        ) dd
JOIN    employee_groups di
ON      di.egid = dd.egid AND di.date = dd.date

Answer 2

aggregate functions will work in mysql, different to the sql standard. to access the value of max(date) from php, you have to alias it:

 SELECT `egid`, `group_name` , `limit`, MAX( `date` ) as maxdate
 FROM …

you can then select it like any other colum from php with

 while($row = mysqli_fetch_row($result)) {
   echo htmlspecialchars($row['maxdate']);
 }

hope that helps