Not getting latest record

[tiggsy]

I'm trying to pick up the latest record for 6 groups, and I'm using the following Select statement:

SELECT * FROM newproducts LEFT JOIN (suppliers, newsrchGrps) ON (suppliers.supplierName = newproducts.supplierName AND newsrchGrps.groupCode = newproducts.groupCode) GROUP BY newproducts.groupCode ORDER BY newproducts.updated DESC LIMIT 6

which does pick up a total of 6 records from 6 different groups, but for some reason I'm not necessarily getting the latest product in each group.

Anyone any idea what's wrong?

[smantscheff]

If you are grouping rows, the non-grouped columns have to be aggregate functions. Otherwise MySQL will pick one row of each group at random (I believe the first one in historical table order).
Consider those examples;

drop table if exists newproducts;
create table newproducts (id integer, groupcode integer, updated integer);
insert into newproducts values (1,1,10),(2,1,9),(3,2,7),(4,2,8);
SELECT * FROM newproducts GROUP BY groupCode ORDER BY updated DESC LIMIT 2;
+----+-----------+---------+
| id | groupcode | updated |
+----+-----------+---------+
|  1 |         1 |      10 |
|  3 |         2 |       7 |
+----+-----------+---------+
SELECT groupcode,updated FROM newproducts GROUP BY groupCode ORDER BY updated DESC LIMIT 2;
+-----------+---------+
| groupcode | updated |
+-----------+---------+
|         1 |      10 |
|         2 |       7 |
+-----------+---------+
SELECT groupcode,max(updated) FROM newproducts GROUP BY groupCode ORDER BY updated DESC LIMIT 2;
+-----------+--------------+
| groupcode | max(updated) |
+-----------+--------------+
|         1 |           10 |
|         2 |            8 |
+-----------+--------------+
SELECT * FROM newproducts where (groupCode,updated) in 
  (select groupcode,max(updated) from newproducts group by groupCode)
;
+----+-----------+---------+
| id | groupcode | updated |
+----+-----------+---------+
|  1 |         1 |      10 |
|  4 |         2 |       8 |
+----+-----------+---------+

[tiggsy]

Hah. That's great! Thanks. It's working now. For others who may have the same problem, I ended up with this:

SELECT * FROM (SELECT * FROM newproducts ORDER BY updated DESC) AS latest LEFT JOIN (suppliers, newsrchGrps) ON (suppliers.supplierName = latest.supplierName AND newsrchGrps.groupCode = latest.groupCode) GROUP BY latest.groupCode LIMIT 6