Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Dec 2003
    Posts
    454

    Unanswered: How can I get the random row from the table?

    When I execute the following query several times, I get the same row if there is no new data inserted:

    SELECT TOP 1 * FROM TableName

    Is there a way to get a random row from the table? Thanks in advance.
    Last edited by gyuan; 01-26-04 at 19:07.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Declare @value As Integer
    set @value = (RAND() * (select count(*) from table))+1

    select *
    from table
    where id = @value
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    If I'm not mistaken this will require that there are no gaps whatsoever in the ID-field which is quite rare I think. You would have to make a while-loop and check if the ID exists I belive, and then loop for each ID that doesn't exist. have never done this myself so there might be a better way...
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    The only issue with that is that you have to have an unique integer for your ID. Additionally your ID can not have gaps and must start at one (or is it zero).

    Perhaps something like this??
    ----------
    Declare @value As Integer
    set @value = (RAND() * (select count(*) from table))+1

    executesql 'select top ' + @value + ' from table order by XYZ'
    ----------

    then you need to select the first one or last one that is selected....

    I don't know how you would do it though....

    HTH

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    If I'm not mistaken this will require that there are no gaps whatsoever in the ID-field which is quite rare I think
    Providing the id column is unique, then

    Declare @value As Integer
    set @value = (RAND() * (select count(*) from table))+1

    select *
    from
    (select columns,
    (select count(*) from table where id <= t1.id) AS ID2
    from table t1) v
    where v.id2 = @value
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Unfortunately I can't test this out on the machine I am on, for the above example wouldn't you need a group by clause in your select count(*) and a having instead of a where clause....

    so something like...

    Declare @value As Integer
    set @value = (RAND() * (select count(*) from table))+1

    select *
    from
    (select columns,
    (select count(*) from table where id <= t1.id group by t1.id) AS ID2
    from table t1) v
    having v.id2 = @value

    once again, sorry I can't check it.

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    No aggregate functions have been computed on the set 'V', meaning that the group by and having clauses are not required.

    Consider,

    Select a, b, (select count(*) from table) AS COUNT
    from table t1
    group by a, b

    This is invalid as COUNT is interpreted as a column as opposed to an aggregate function of t1.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Okie cool. Like I said, I couldn't check so.

    It's an interesting problem though... personally I wouldn't try and get the database to do this....

    I'd get the app to generate a random id to select and just do a standard query on that id...

    Each to their own though.

  9. #9
    Join Date
    Dec 2003
    Posts
    454
    Thank you for your all helps.

    Since the ID field (primary key) does not start at one and also there may be a gap in this field (some data may be deleted), I modified the query posted by r123456

    Declare @value As Integer
    SET @value = (RAND() * (SELECT Count(*) FROM Users)) + 1

    SELECT TOP 1 *
    FROM Users
    WHERE UserID >= @value
    ORDER BY UserID

    How do you think about it?
    Last edited by gyuan; 01-26-04 at 23:07.

  10. #10
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    Try this and see:
    SELECT TOP 1 * FROM TableName
    order by newid()
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  11. #11
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Originally posted by gyuan
    Thank you for your all helps.

    Since the ID field (primary key) does not start at one and also there may be a gap in this field (some data may be deleted), I modified the query posted by r123456

    Declare @value As Integer
    SET @value = (RAND() * (SELECT Count(*) FROM Users)) + 1

    SELECT TOP 1 *
    FROM Users
    WHERE UserID >= @value
    ORDER BY UserID

    How do you think about it?
    The code above will still encounter problems with the gaps and the not starting at zero....

    this is the one you want

    Originally posted by r123456

    Declare @value As Integer
    set @value = (RAND() * (select count(*) from table))+1

    select *
    from
    (select columns,
    (select count(*) from table where id <= t1.id) AS ID2
    from table t1) v
    where v.id2 = @value

  12. #12
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    The problem you get with this solution

    Declare @value As Integer
    SET @value = (RAND() * (SELECT Count(*) FROM Users)) + 1

    SELECT TOP 1 *
    FROM Users
    WHERE UserID >= @value
    ORDER BY UserID

    is say you have 5000 records and you delete 4000 records.

    Your rand value will be between 1 and 4000 but your max UserId is 5000 anything with an Id over 4000 is pretty much unreachable...

    I think you'd be better with this....

    Declare @value As Integer
    SET @value = (RAND() * (SELECT max(UserID) FROM Users)) + 1

    SELECT TOP 1 *
    FROM Users
    WHERE UserID >= @value
    ORDER BY UserID

    It would mean when gaps occur the row after the gap would be hit more often, but atleast you would cover your entire collection of rows.

    Hope that makes sense.

  13. #13
    Join Date
    Dec 2003
    Posts
    454
    quote:
    --------------------------------------------------------------------------------
    Originally posted by r123456

    Declare @value As Integer
    set @value = (RAND() * (select count(*) from table))+1

    select *
    from
    (select columns,
    (select count(*) from table where id <= t1.id) AS ID2
    from table t1) v
    where v.id2 = @value

    --------------------------------------------------------------------------------

    What does columns stand for in the query?

  14. #14
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    errr....won't my simple statement solve it???

    SELECT TOP 1 * FROM TableName
    order by newid()

    I don't get it....
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  15. #15
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    the columns you want to select eg * or username, firstname, lastname etc...

Posting Permissions

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