SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot ozonew4m's Avatar
    Join Date
    Apr 2006
    Location
    at my desk
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    random distinct or group by or perhaps order by?

    Back again with another problem query...

    I have a table called urls
    in this table i have 4 fields:
    url_id (primary key)
    url_url - (unique)
    url_ module
    url_title

    the url_module field will be one of 5 values:
    forum
    products
    links
    blog
    gallery

    what im trying to do is select a random row for each url_module type so that the result will consist of 1 random forum url, 1 random products url, 1 random links url, 1 random blogs url and 1 random gallery url..

    does that make sense? and is it possible to achieve this in one query using group by or order by or distinct or ...

    I would appreciate any help or pointing in the right direction

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,325
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    here's one method --
    Code:
    ( SELECT url_url, url_module, url_title
        FROM urls
       WHERE url_module = 'forum'
      ORDER BY RAND() LIMIT 1 )
    UNION ALL
    ( SELECT url_url, url_module, url_title
        FROM urls
       WHERE url_module = 'products'
      ORDER BY RAND() LIMIT 1 )
    UNION ALL
    .
    .
    .
    this is 5 times as slow, however, as pulling say a dozen random rows and looping through them to find the 5 different module types

    (yes, there is a chance that you won't find all 5, but that's a small chance, and all you have to do is loop back and get a dozen more when that happens)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •