MySQL Version 4.0.20 SELECT MAX() Bug?

[PStoneMan]

I have something very strange happening and I need to know if this is a possible bug or simply an ID 10 T ERROR.

I am trying to select the most recent sale date from a table of real estate transfers.

Very simply (there are more fields but these will be enough to show what is happening) I run the following query:

select parcel_key, sale_date, sortable_sale_date from Transfers where parcel_key=8;

And it produces the following:

+------------+-------------+--------------------+
| parcel_key | sale_date | sortable_sale_date |
+------------+-------------+--------------------+
| 8 | Sep-28-1999 | 19990928 |
| 8 | Jun-28-2002 | 20020628 |
+------------+-------------+--------------------+


Now notice the sale date and sortable sale date. These match up as they should.

Now here is the problem. I run this query:

select parcel_key, sale_date, max(sortable_sale_date) from Transfers where parcel_key=8 group by parcel_key;

and I get the following results:

+------------+-------------+-------------------------+
| parcel_key | sale_date | max(sortable_sale_date) |
+------------+-------------+-------------------------+
| 8 | Sep-28-1999 | 20020628 |
+------------+-------------+-------------------------+

It has taken the sale date from the one record and merged it with the sortable sale date of the second record. In my mind I should have received the second rom of data from the first query above, the one with the sale date of Jun-28-2002.

Now I must say I have been programming with SQL for over 5 years and I can honestly say I have never seen this happen before. However, that time has been spent using Microsoft Products.

So is this a bug or an ID 10 T error. Please help.

Thanks

Paul

 

[swampBoogie]

The problem is that your group by query is not properly written. Any other DBMS would have said that is invalid since you have non-aggregated columns in the select clause that is not in the group by clause. Mysql allows it and will chose an arbitrary row within each group for the salesdate column.

To get the result you are looking for you need to do a self-join

select t1.parcel_key, t1.sale_date, t1.sortable_sale_date
from t as t1 
inner join t t2 on 
t1.parcel_key = t2.parcel_key
where t1.parcel_key = 8
group by t1.parcel_key, t1.sale_date, t1.sortable_sale_date
having t1.sortable_sale_date = max(t2.sortable_sale_date)

Preferably I would write this type of query as

select * from t as t1
where parcel_key = 8
and sortable_sale_date = (
select max(sortable_sale_date) from t
where t.parcel_key = t1.parcel_key)

but that is not supported in the current Mysql version.

 

[PStoneMan]

Yep it was an ID 10 T error. Thanks swapboogie for point out my error to me. Your solution worked perfectly