1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Sep 2012
    Rep Power

    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
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Columbus, OH
    Rep Power
    so, this is my test table like yours
    create table #T
    id int,
    Job varchar(50),
    Category varchar(50)
    I inserted your test data

    insert into #T
    select 1,'Clerk','Admin'
    2,	'Secretary' 	,'Admin'
    select 3, 	'Reception' 	,'Admin'
    select 4, 	'Typist' 	,'Admin'
    this is a final code for get random 5 records
    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
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree

Similar Threads

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

IMN logo majestic logo threadwatch logo seochat tools logo