SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    Aveiro, Portugal
    Posts
    165
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Limit number of results by foreign key

    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.
    Yours truely
    Mário Ramos

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Try a select on EACH make, with limit 5, and union to the next select on the next make. etc.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mjpr View Post
    ... 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    Aveiro, Portugal
    Posts
    165
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The latest five.

    My query so far is:

    Code MySQL:
    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.
    Yours truely
    Mário Ramos

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mjpr View Post
    The latest five.
    ah, excellent

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

    let's start here --
    Code:
    WHERE ...
    a.sitecatid = b2.catid AND b2.catid IN (9,12,65)
    obviously, this allows us to transpose, so that we can write --
    Code:
    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 --
    Code:
    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?????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    Aveiro, Portugal
    Posts
    165
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code MySQL:
    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.
    Yours truely
    Mário Ramos

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mjpr View Post
    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 --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    Aveiro, Portugal
    Posts
    165
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes r937.

    That gives me all the ads that meet the constraints, for each given category.
    Yours truely
    Mário Ramos

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, here ya go...
    Code:
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    Aveiro, Portugal
    Posts
    165
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937!

    You have helped me out a lot.

    It's working perfectly and very fast too.
    Yours truely
    Mário Ramos


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •