modified solution, seems to work now :)

[Markus Krebs]

after some modifications, this query seems to work now:

select t1.id,t1.firmid,ltrim(concat(t1.brand,' ',t1.type)) as title,concat(firm.name,' | ',firm.phone,' | ',firm.homepage) as firmdata, t1.country,t1.picid, t1.ps, t1.width, t1.volume, t1.capacity, t1.year,t1.hours, t1.price,t1.vatoption, t1.highlight from offer t1 left join offer t2 ON t1.firmid=t2.firmid AND t1.created < t2.created left join firm on t1.firmid=firm.id where t2.firmid IS NULL and t1.active=1 and t1.price > 1 order by t1.created desc limit 10;

thats the query in a whole.
the short form is:

select t1.*
from offer t1
left join offer t2 ON t1.firmid=t2.firmid AND t1.created < t2.created
where t2.firmid IS NULL
order by t1.created desc
limit 10;

so you see, the final sort on the timestamp column did the trick.

output:

+------+--------+---------------------+
| id | firmid | created |
+------+--------+---------------------+
| 1265 | 172 | 2007-01-15 16:09:53 |
| 1263 | 151 | 2007-01-15 08:19:08 |
| 1260 | 177 | 2007-01-10 09:11:37 |
| 1258 | 166 | 2007-01-09 14:18:05 |
| 1257 | 167 | 2007-01-09 09:56:22 |
| 1231 | 171 | 2006-12-26 23:21:14 |
| 1154 | 157 | 2006-12-15 09:20:38 |
| 1151 | 174 | 2006-12-12 15:57:13 |
| 1150 | 169 | 2006-12-08 17:56:33 |
| 1145 | 190 | 2006-11-30 09:08:04 |
+------+--------+---------------------+
10 rows in set (0.13 sec)


thanks & cu,
Markus