Description:
I am having the following case which is returning discrepancies when using GROUP BY function.
A - These are all the rows for the SUBJECT ='Para Regina'
mysql> select received, id, senderid, subject
-> from discussao
-> where subject='Para Regina';
+---------------------+------+----------+-------------+
| received | id | senderid | subject |
+---------------------+------+----------+-------------+
| 2003-09-28 17:56:43 | 4928 | 1218 | Para Regina |
| 2003-09-30 18:54:51 | 4965 | 1276 | Para Regina |
| 2003-10-01 04:22:06 | 4971 | 6509 | Para Regina |
| 2003-10-01 17:59:59 | 4983 | 1276 | Para Regina |
+---------------------+------+----------+-------------+
4 rows in set (0.00 sec)
>> All values returned are correct
B - Here is the first event of the SUBJECT in a period (min (received))
mysql> select min(received) received, id, senderid, subject
-> from discussao
-> where subject='Para Regina'
-> group by subject;
+---------------------+------+----------+-------------+
| received | id | senderid | subject |
+---------------------+------+----------+-------------+
| 2003-09-28 17:56:43 | 4928 | 1218 | Para Regina |
+---------------------+------+----------+-------------+
1 row in set (0.00 sec)
>>> The result is right
C - this is the row returned for the last event for the SUBJECT ('Para Regina')
mysql> select max(received) received, id, senderid, subject
-> from discussao
-> where subject='Para Regina'
-> group by subject;
+---------------------+------+----------+-------------+
| received | id | senderid | subject |
+---------------------+------+----------+-------------+
| 2003-10-01 17:59:59 | 4928 | 1218 | Para Regina |
+---------------------+------+----------+-------------+
1 row in set
>> Here, the result is wrong!
Accordingly to the table above, it should return the row:
| 2003-10-01 17:59:59 | 4983 | 1276 | Para Regina |
the RECEIVED and SUBJECT are correct, but not ID and SENDERID
Could you check this for me?
Kind Regards
Paulo Habl
How to repeat:
Create a MyIsam table with the above data and execute similar queries.
Description: I am having the following case which is returning discrepancies when using GROUP BY function. A - These are all the rows for the SUBJECT ='Para Regina' mysql> select received, id, senderid, subject -> from discussao -> where subject='Para Regina'; +---------------------+------+----------+-------------+ | received | id | senderid | subject | +---------------------+------+----------+-------------+ | 2003-09-28 17:56:43 | 4928 | 1218 | Para Regina | | 2003-09-30 18:54:51 | 4965 | 1276 | Para Regina | | 2003-10-01 04:22:06 | 4971 | 6509 | Para Regina | | 2003-10-01 17:59:59 | 4983 | 1276 | Para Regina | +---------------------+------+----------+-------------+ 4 rows in set (0.00 sec) >> All values returned are correct B - Here is the first event of the SUBJECT in a period (min (received)) mysql> select min(received) received, id, senderid, subject -> from discussao -> where subject='Para Regina' -> group by subject; +---------------------+------+----------+-------------+ | received | id | senderid | subject | +---------------------+------+----------+-------------+ | 2003-09-28 17:56:43 | 4928 | 1218 | Para Regina | +---------------------+------+----------+-------------+ 1 row in set (0.00 sec) >>> The result is right C - this is the row returned for the last event for the SUBJECT ('Para Regina') mysql> select max(received) received, id, senderid, subject -> from discussao -> where subject='Para Regina' -> group by subject; +---------------------+------+----------+-------------+ | received | id | senderid | subject | +---------------------+------+----------+-------------+ | 2003-10-01 17:59:59 | 4928 | 1218 | Para Regina | +---------------------+------+----------+-------------+ 1 row in set >> Here, the result is wrong! Accordingly to the table above, it should return the row: | 2003-10-01 17:59:59 | 4983 | 1276 | Para Regina | the RECEIVED and SUBJECT are correct, but not ID and SENDERID Could you check this for me? Kind Regards Paulo Habl How to repeat: Create a MyIsam table with the above data and execute similar queries.