Get randomically or row from a table

Question

I need you help...for a little problem. I have a java service that should access in a table and get a random row from table.

My table is simply: it contains only two cols:

"Id"        INT IDENTITY(1,1) NOT NULL Primary Key
"Datas"     Varchar(64)       NOT NULL

Values Id is an progressive number, so you should think it could be enough to create a random number and get the row where id=randomic_number.

But I have lots of gap in table. So for example, a sample of table could be this:

  ID    Datas
  1     Row1
  2     Row2
  3     Row3
  8     Row4
 10     Row5
 25     Row6
639     Row7

Is there a very stylish way to get one row randomly? No condition must be...only random! I use sql srv 2000.

I would avoid to to...

select *

and then cycling the entire Resultset using a random number...because it can contain a very large number of rows....

Answer 1

You should be able to do something along the lines of:

SELECT TOP 1 * FROM mytable ORDER BY newid()

Note: this is a duplicate of #52964 which is in turn a duplicate of #19412

Answer 2

I would suggest to get the last id in the table like so

SELECT TOP 1 Id FROM table_name ORDER BY Id DESC

then assuming this is stored in the maxId variable, you could generate a random number index between 1 and maxId and do :

SELECT TOP 1 * FROM table_name WHERE Id > index

And that's it