SELECT b.catname AS marca, a.sitetitle AS modelo, a.custom_field_1 AS cilindrada, a.custom_field_2 AS cv,
a.custom_field_3 AS portas, a.siteid AS id, a.custom_field_4 AS lugares, a.custom_field_5 AS ano, a.custom_field_7 AS cor,
a.custom_field_8 AS km, a.custom_field_9 AS preco, a.custom_field_10 AS localizacao, a.custom_field_11 AS tel, a.dateinsert,
count(*) AS n
FROM ad a, user u, category b
JOIN category b2 ON b2.catid >= b.catid AND b.catname=b2.catname
WHERE a.sitecatid = b2.catid
AND b2.catid IN (9,12,65)
AND u.email = a.ad_username
AND a.custom_field_1 <> ''
AND a.custom_field_2 <> ''
AND a.valid = 1
AND a.sold = ''
AND a.dateinsert BETWEEN '2009-12-28' AND '2010-01-28'
GROUP BY b.catid, a.sitetitle
HAVING count(*) < 5
It’s working, but it doesn’t limit the ads to 5 for each category.
let’s clean up the query before attempting to add the “latest 5” condition
please try this and confirm that it gives you all ads for each category –
SELECT b.catname AS marca
, a.sitetitle AS modelo
, a.custom_field_1 AS cilindrada
, a.custom_field_2 AS cv
, a.custom_field_3 AS portas
, a.siteid AS id
, a.custom_field_4 AS lugares
, a.custom_field_5 AS ano
, a.custom_field_7 AS cor
, a.custom_field_8 AS km
, a.custom_field_9 AS preco
, a.custom_field_10 AS localizacao
, a.custom_field_11 AS tel
, a.dateinsert
FROM ad a
INNER
JOIN category b
ON b.catid = a.sitecatid
WHERE a.sitecatid IN (9,12,65)
AND a.custom_field_1 <> ''
AND a.custom_field_2 <> ''
AND a.valid = 1
AND a.sold = ''
AND a.dateinsert BETWEEN '2009-12-28' AND '2010-01-28'
ORDER
BY b.catname ASC
, a.dateinsert DESC
notice i removed the user table from the query because you weren’t using it
SELECT b.catname AS marca
, a.sitetitle AS modelo
, a.custom_field_1 AS cilindrada
, a.custom_field_2 AS cv
, a.custom_field_3 AS portas
, a.siteid AS id
, a.custom_field_4 AS lugares
, a.custom_field_5 AS ano
, a.custom_field_7 AS cor
, a.custom_field_8 AS km
, a.custom_field_9 AS preco
, a.custom_field_10 AS localizacao
, a.custom_field_11 AS tel
, a.dateinsert
FROM ad a
INNER
JOIN category b
ON b.catid = a.sitecatid
WHERE a.sitecatid IN (9,12,65)
AND a.custom_field_1 <> ''
AND a.custom_field_2 <> ''
AND a.valid = 1
AND a.sold = ''
AND a.dateinsert BETWEEN '2009-12-28' AND '2010-01-28'
[COLOR="Blue"]AND ( SELECT COUNT(*)
FROM ad
WHERE custom_field_1 <> ''
AND custom_field_2 <> ''
AND valid = 1
AND sold = ''
AND dateinsert BETWEEN '2009-12-28' AND '2010-01-28'
AND sitecatid = a.sitecatid
AND dateinsert > a.dateinsert )
< 5[/COLOR]
ORDER
BY b.catname ASC
, a.dateinsert DESC
for each ad, just count the number of other ads with the same qualifications, in the same categry, with an earlier date
if the number of earlier ads is less than 5, then the current ad must be one of the latest 5