Take the 2-minute tour ×

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`)

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).

share|improve this question
Can you post the PHP code you're using? –  awgy May 8 '10 at 11:25
"not how I expect it" What did you expect, and what did you get? –  Mark Byers May 8 '10 at 13:45
add comment

2 Answers

up vote 1 down vote accepted

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
share|improve this answer
mark, again. this is mysql. in mysql you can select fields outside of the group with*out* using an aggregate function. i know this does not work in standard sql and most implementations, but it does work in mysql (the actual row to be returned is undefined [most of the time it's the first row], but it compiles, works and returns a resultset!) –  knittl May 8 '10 at 13:32
This works great!!! Thank you. I posted my question on three other forums but nobody was able to help me. You are a smart man, Mark. –  kdobrev May 9 '10 at 9:34
@kdobrev: Glad to help. Remember to accept an answer when your problem is solved. Accepting answers will encourage people here to help you more. :) –  Mark Byers May 9 '10 at 10:01
add comment

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

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

share|improve this answer
Mark's answer was what I was looking for. Thanks anyway. –  kdobrev May 9 '10 at 9:36
add comment

Your Answer


