Re: Pulling a blank SQL row, but there are no blank rows.

The usual way of getting random records by using newid() (which is a function that returns a GUID each time it is called) is

select top 10 
newid() as randomrow, productid, productname 
from Products
order by randomrow
[download]

(which gets you 10 random records)

Try changing your SQL-code to:

select top 1 
Quote, CharacterName, GameTitle, newid() as randomrow  
from dbo.GameQuotes 
order by randomrow
[download]

If that doesn't work there must somehow be an empty row in your database.

Update: Of course the above "solution" scales very badly; so in general do not use it if your database contains many records. It "works" by giving all your records a random GUID (itself a rather expensive function), then doing a sort on this value which is NOT indexed and therefore very slow and finally throwing everything away but the first record.

Much better is to add an extra field in your database which has a sequence-number from 1 to whatever number of records you have. Most databases can do this automatically. Then just generate a random integer in the range 1 to number of records and SELECT the record with that sequence number. Much, much faster and less strain on your database server.

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

Comment on Re: Pulling a blank SQL row, but there are no blank rows. Select or Download Code

Replies are listed 'Best First'.
Re^2: Pulling a blank SQL row, but there are no blank rows. by afoken (Chancellor) on Jun 14, 2009 at 07:21 UTC
I think the randomrow column is not needed. Just `order by random()` (or `order by rand()` for MS SQL Server). Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply] [d/l] [select]
Re^3: Pulling a blank SQL row, but there are no blank rows. by CountZero (Bishop) on Jun 14, 2009 at 07:30 UTC
I'm not sure for SQL Server 2008, but earlier versions of SQL-server would return the same value for `rand()` for all records within one select-statement. Other types of database, recalculate `rand()` for every record, as one would expect. CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply] [d/l] [select]
Re^4: Pulling a blank SQL row, but there are no blank rows. by afoken (Chancellor) on Jun 14, 2009 at 15:12 UTC
I wasn't aware of that behaviour, and it seems to be undocumented. At least, in the SQL Server 2000 documentation of the rand() function, there is no trace of a document describing this behaviour. Unfortunately, I currently don't have access to any SQL Server installation. If the SQL Server really behaves that brainf*ed (and I have no problem to believe that), using rand() would not help. But I don't think that newid() gives sufficiently random output. It seems to return a UUID, which can be constructed from a machine-specific constant and the local time, at least in v1 and v2. v3 and v5 are hash functions, only v4 is really random. Again, I could not find a document stating how newid() is implemented. Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply]