Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: Help with displaying random records in MySQL

Results 1 to 2 of 2
Share This Thread →
  1. #1
    DanCorb is offline Registered User
    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0

    Help with displaying random records in MySQL

    I have the following table:
    ID Job Category
    1 Clerk Admin
    2 Secretary Admin
    3 Reception Admin
    4 Typist Admin

    Now, I want to display 5 jobs within the Admin Category, but I want them pulled randomly from the database. ORDER BY RAND won't work because there are only 4 records. I need to display 5 records as it doesn't matter if the same record is displayed more than once. How do I do this?

  2. #2
    gk53's Avatar
    gk53 is offline Contributing User
    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,538
    Rep Power
    278
    so, this is my test table like yours
    Code:
    create table #T
    (
    id int,
    Job varchar(50),
    Category varchar(50)
    )
    I inserted your test data

    Code:
    insert into #T
    select 1,'Clerk','Admin'
    union
    select 
    2,	'Secretary' 	,'Admin'
    union
    select 3, 	'Reception' 	,'Admin'
    union
    select 4, 	'Typist' 	,'Admin'
    this is a final code for get random 5 records
    Code:
    select top 5 t.* from
    (select *, FLOOR(RAND() * 1000000) as row from #T 
    union all
    select *, FLOOR(RAND() * 1000000) as row from #T) t
    order by row
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

Similar Threads

  1. Random records
    By webb in forum ASP Development
    Replies: 5
    Last Post: April 8th, 2009, 04:17 AM
  2. displaying random colors to whole row!!
    By micky in forum .NET Development
    Replies: 12
    Last Post: August 20th, 2005, 12:05 PM
  3. Displaying random images and text from database
    By multichild in forum ASP Development
    Replies: 5
    Last Post: January 9th, 2005, 10:29 PM
  4. 4 random records from db
    By marcvp in forum ASP Development
    Replies: 1
    Last Post: August 6th, 2004, 08:55 AM
  5. random records
    By Steve Schofield in forum ASP Development
    Replies: 2
    Last Post: July 2nd, 2001, 07:51 AM

ASP Free Advertisers and Affiliates