Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Location
    Oklahoma City, Oklahoma
    Posts
    39

    Unanswered: LIMIT, rand() and DISTINCT problems.

    We are having problems displaying random results from our database.

    THE WANTED OUTCOME:
    We need to distinctivly randomly retrieve 3 results from our table.

    THE CODE:
    PHP Code:
    $get_ads $mysqli->query("SELECT DISTINCT advertisers_id, id, image, pointurl, impressions, killimpressions, killdate FROM banners GROUP BY advertisers_id ORDER BY rand() LIMIT 3") or die($mysqli->error); 
    THE PROBLEM:
    The query is selecting the top 3 distinct results ... then randomizing them. We need the opposite. We need to select 3 random rows then limit them by 3.

    MySQL Version 4.1

    Please help! Thanks!!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you must approach this problem from two different directions

    first, let us discuss what you mean by distinct

    since each banner has a distinct id, you are always going to get three distinct banners

    so obviously, you must mean something else, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Location
    Oklahoma City, Oklahoma
    Posts
    39
    Whoops, I am sorry. I have two ID's. A banner ID (which is the primary key) and the advertisers_id (which gives relation to the advertisers table)

    I need to select 3 distinct rows BY unique/distinct advertisers_id

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    we need to be a bit more precise

    you want 3 banners, but each from a different advertiser, correct?

    so it is the 3 advertisers that must be unique, not the banners
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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