1

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....

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user3914783
  • 35
  • 1
  • 4
  • see http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/ – Leo Aug 11 '14 at 13:37
  • What database are you using? I assume MSSQL (due to the `IDENTITY` keyword)? – exhuma Aug 11 '14 at 13:38
  • Alright. Thanks for clarifying. I added the `tsql` tag to reflect this. – exhuma Aug 11 '14 at 13:47
  • possible duplicate of [How to request a random row in SQL?](http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql) – exhuma Aug 11 '14 at 13:53
  • EXHUMA!!! FANTASTIC!!! SELECT TOP 1 column FROM table ORDER BY NEWID() THANK you VERY MUCH!!! – user3914783 Aug 11 '14 at 13:59

2 Answers2

4

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

Community
  • 1
  • 1
exhuma
  • 20,071
  • 12
  • 90
  • 123
  • 1
    In SQL, the `rand` function will generate one random value for the statement and apply that one value to every row returned. – Philip Kelley Aug 11 '14 at 13:35
  • Great answer, I just tried it on PostgreSQL and it works. Using the database to return the random row is the best option as doing it in java on the client will not be nearly as elegant or efficient – Shawn Vader Aug 11 '14 at 13:36
  • If performance matters then this will cause an end-to-end scan of the entire table. There are ways to avoid it, eg. see [Limiting Result Sets by Using TABLESAMPLE](http://technet.microsoft.com/en-us/library/ms189108(v=sql.105).aspx) – Remus Rusanu Aug 11 '14 at 13:37
  • RANDOM() is not a valid function in sql serv2000.... I tried with SELECT Top 1 * FROM MyTable ORDER BY RAND() but it's not randomic! It return always the same row... :( – user3914783 Aug 11 '14 at 13:38
  • @RemusRusanu I thought `TABLESAMPLE` is not truly random. But my experience with it is flaky. It really depends on your use-case. As far as I remember, if you want "truly" random results, you should use `NEWID()` instead of `TABLESAMPLE`. Correct me if I'm wrong... – exhuma Aug 11 '14 at 13:44
  • @user3914783 Yes. My bad. I fixed it. The reason here is that `RAND()` is evaluated before the query is executed. Essentially, ending up with a query like `SELECT * FROM table ORDER BY 3453` (where 3453 is the random number). – exhuma Aug 11 '14 at 13:45
  • `Newid` is, indeed, the way to go in SQL. I prefer `checksum(newid())`, as that further randomizes however sequential a series of guids might be. – Philip Kelley Aug 11 '14 at 13:46
  • 1
    @PhilipKelley How does `checksum` further randomise this? To my knowledge `checksum` is a -- *ducks* -- hashing function (i.e. deterministic). So the same ID will always return the same result. I don't see how that would aid in randomisation. – exhuma Aug 11 '14 at 13:49
  • 1
    Indeed `TABLESAMPLE` is not completely random, as the unit of selection is page not a row. But if you have significant data size then the trade off is necessary. One can combine TABLESAMPLE with top(1) ... order by newid() to get 'quite random' w/o paying the penalty of large data scans. If the table is small then why bother, indeed... – Remus Rusanu Aug 11 '14 at 13:54
  • 1
    @PhilipKelley `newid()` guids have no 'sequentialness' whatsoever. for all purposes, bare crypto, they are as random as it gets. – Remus Rusanu Aug 11 '14 at 13:56
  • EXHUMA!!! FANTASTIC!!! SELECT TOP 1 column FROM table ORDER BY NEWID() THANK you VERY MUCH!!! – user3914783 Aug 11 '14 at 14:00
  • I've never been able to track down the actual algorithm for generating a guid (anyone got a link?) but I do know they are based on internal time and "NIC card", or some such. They are not random in the true sense, though they are certainly random enough for things like this. (And I got a bit confused with the more recent "sequential guid" function.) If sequential, checksum would at least de-sequentialize them, since it would be a hash on different values. – Philip Kelley Aug 11 '14 at 14:03
0

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

webNeat
  • 2,768
  • 1
  • 20
  • 22
  • Not randomic at all if I have a big gap. For example: I have the following ID: 1,2,3,650,651,652,etc... Randomic numbers beetween 4 and 649...will return me always the same row (n° 650)... – user3914783 Aug 11 '14 at 13:56