How 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.Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1

select *
from table
where id = @.value|||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...|||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|||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|||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.|||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.|||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. :)|||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?|||Try this and see:
SELECT TOP 1 * FROM TableName
order by newid()|||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|||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.|||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?|||errr...won't my simple statement solve it?

SELECT TOP 1 * FROM TableName
order by newid()

I don't get it...|||the columns you want to select eg * or username, firstname, lastname etc...|||Hi Patrick,

I'm really not sure how your solution would work, what is the newid()?|||Its a build in command specifically to be use to "select random rows".
It is also use as a comand to auto gen Unique Identifier ids.

But when use in "order by newid()" it generates random rows.

Try it out and see...it works...unless..well theres something I'm missing in the whole discussion.|||Originally posted by Patrick Chua
errr...won't my simple statement solve it?

SELECT TOP 1 * FROM TableName
order by newid()

I don't get it...

It works too, but it takes 2 seconds, a little slower. Now I know the function NewID() and it helps. Thanks.|||rokslide,

That is a good idea to replace Count(*) to MAX(UserID). Thanks.|||Cool, I think I'll have to have a look into that one some time. :)

Thanks Patrick. :)|||No worries gyuan, please note though that (as I said above) it's not truely random.|||Correct.

Count(*) should not be replaced with max(id). The reason being that should a "gap" occur then the probability is increased for those values that occur past the "gap".

If TOP * 1 is used in conjunction with max(id) then only the first value past a "gap" value will be returned, should @.value be equal to a "gap" value.|||I think Patrick's query is better:

SELECT TOP 1 *
FROM Users
ORDER BY NewID()

although the running time is a little longer.|||I've used Patrick's method in the past with success.|||Isn't anyone going to ask WHY do you want to do this?|||I use Patricks method daily and it works out great for me.
NewID() takes a little longer due to it generates a GUID but it is truely random.|||Originally posted by Brett Kaiser
Isn't anyone going to ask WHY do you want to do this?

Brett ...
You are always after the "why" instead of the "how"? I like you for the spirit coz I believe "Prevention is better than cure".|||How's easy.....

And thanks...

Seen to many rocket ships built...|||I have to admit to orginally thinking why would you want to, but I have seen a few "scuffles" break out on here over the "why" of things so I decided to leave it alone.

Then of course the curiosity took over and I started to think,... hey, how would you do that...

anyhow...

r123456

with this statement...

Count(*) should not be replaced with max(id). The reason being that should a "gap" occur then the probability is increased for those values that occur past the "gap".

but if you use count and then compare count to the ID value you are going to completely miss some sections of the data entirely (eg. they will never have a chance to be selected). See the example that I noted eariler. The the max(id) option atleast you cover your entire span of data.

Of course Patrick's solution will cover everything perfectly so....|||Not true.

ID | ID2
1 1
2 2
3 3
4 4
5 5

Delete from table where id=2 OR id=3;

ID | ID2
1 1
4 2
5 3

You have two solutions. One of which requires very little code and an SQL Server function. The other requires a unique id, which for example can be the value of ROWID for an Oracle database.|||Ah yes, sorry, I forgot the second version of your solution with the new ID. :)

My fault entirely.