Limit number of results by foreign key

[mjpr]

Hi!

Imagine I have a table of ads.
Each ad has a category, which is a car Make.

I have a query to extract all ads between today and last month.
What I need is to apply a constraint that will only give me 5 ads of each make.

So if I have in my ads table:

10 Audi
20 BMW
30 Ford
17 Lexus

I only get 5 of each.

Thankx for any help.

[Dr John]

Try a select on EACH make, with limit 5, and union to the next select on the next make. etc.

[r937]

... only give me 5 ads of each make.

which 5? the biggest? the ones with the longest names? the tallest? the earliest?

you would need to specify some column, the values of which can be used to determine a ranking, and then you can take the top 5 based on that ranking

[mjpr]

The latest five.

My query so far is:

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.

[r937]

The latest five.

ah, excellent

now i need to understand exactly what you're doing with that query

let's start here --

WHERE ...
a.sitecatid = b2.catid AND b2.catid IN (9,12,65)

obviously, this allows us to transpose, so that we can write --

WHERE ...
a.sitecatid IN (9,12,65) AND b2.catid = a.sitecatid

so you want only those ads which are in categories 9, 12, or 65

now comes the weird part

just why do you need a second category for each ad, with the following condition --

WHERE ...
b2.catid >= b.catid AND b.catname=b2.catname

i am completely flummoxed

what's going on here? a different category with the same name?????

[mjpr]

WHERE ...
b2.catid >= b.catid AND b.catname=b2.catname

That was something I picked up yesterday from here.

But it's not in my WHERE clause. It's in the JOIN.

[r937]

That was something I picked up yesterday from here.

i think you mis-applied the concept

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

[mjpr]

Yes r937.

That gives me all the ads that meet the constraints, for each given category.

[r937]

okay, here ya go...

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'
   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
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

this works correctly for ties, too

[mjpr]

Thanks r937!

You have helped me out a lot.

It's working perfectly and very fast too.