MySQL `HAVING` issue

I have two queries which are returning different results when I would expect them to return the same results.

The first query returns the correct result. The second returns a result, but it is incorrect.

Why is this and how can I fix the second statement so that it returns the same result? I have to use the HAVING clause in this statement.

1.

    SELECT
        CAST(CONCAT(DATE(`mytable`.`starttime`),' ',HOUR(`mytable`.`starttime`),':',LPAD(60*(MINUTE(`mytable`.`starttime`) DIV 60),2,'0'),':00') AS DATETIME) AS `date`,
        `mytable`.`id`
    FROM
        `mytable`
    WHERE
        `mytable`.`starttime`>='2011-07-01 00:00:00'
        AND `mytable`.`starttime`<='2011-07-01 23:59:59'
        AND `id` BETWEEN 1 AND 100
    GROUP BY
        `mytable`.`id`

2.

    SELECT
        CAST(CONCAT(DATE(`mytable`.`starttime`),' ',HOUR(`mytable`.`starttime`),':',LPAD(60*(MINUTE(`mytable`.`starttime`) DIV 60),2,'0'),':00') AS DATETIME) AS `date`,
        `mytable`.`id`
      FROM
        `mytable`
    WHERE 
        `id` BETWEEN 1 AND 100
    GROUP BY
        `mytable`.`id`
    HAVING `date` IN ('2011-07-01 00:00:00', '2011-07-01 01:00:00', '2011-07-01 02:00:00', '2011-07-01 03:00:00', '2011-07-01 04:00:00', '2011-07-01 05:00:00', '2011-07-01 06:00:00', '2011-07-01 07:00:00', '2011-07-01 08:00:00', '2011-07-01 09:00:00', '2011-07-01 10:00:00', '2011-07-01 11:00:00', '2011-07-01 12:00:00', '2011-07-01 13:00:00', '2011-07-01 14:00:00', '2011-07-01 15:00:00', '2011-07-01 16:00:00', '2011-07-01 17:00:00', '2011-07-01 18:00:00', '2011-07-01 19:00:00', '2011-07-01 20:00:00', '2011-07-01 21:00:00', '2011-07-01 22:00:00', '2011-07-01 23:00:00')

Thanks in advance for any help you can offer.

WHERE clause is applied before GROUPing while HAVING is applied after. So in your second query where you have GROUP BY with no WHERE clause MySql returns a random (undetermined) single row and then applies the HAVING clause to it.

Group by is used with aggregate functions - sum, min, max, count. Your query doesn't appear to have an aggregate in the "select" - so group by doesn't do anything.

Whilst your query may be valid SQL, it doesn't make sense. Not sure if this is why your having clause is going nuts, though.

I assume "id" is a primary key on your table, is that right? I'm guessing that you want to show the times during the day at which various events occurred but I'm not sure why you would group by id then. Could you give an example of how you would like the output to look?