Graham
|
| Posted: 05/02/2005, 2:46 PM |
|
Hi all,
I need some help please.
I need to select 5 random records from a table. The criteria is that the field "active" has to be set to a 1 to be included in the result. Thus if active=0, it should not be included in the random result.
I have it working on Windows XP with an Access Databese, but can not get it to work won windows 2003 server and MSSql 2000.
My code as follows:
SELECT TOP 5 *
FROM books
WHERE
active =1
ORDER BY
Rnd(-(Second(Now())*book_id))
|
|
|
 |
Nicole
Posts: 586
|
| Posted: 05/03/2005, 7:49 AM |
|
Graham,
Provided query is using Access specific database functions now(), second(), rnd(). As you’re using SQL Server database you need to use database functions specific for it.
_________________
Regards,
Nicole |
 |
|
Graham
|
| Posted: 05/03/2005, 12:23 PM |
|
Thanks Nicole,
But what are they? I'm not a SQL man yet.. 
Thanks
|
|
|
|
Nicole
Posts: 586
|
| Posted: 05/04/2005, 4:44 AM |
|
I’m not an expert in it too :). But you can use the following function, I found there descriptions in MSDN Transact-SQL Reference
GETDATE() - returns current date
http://msdn.microsoft.com/library/default.asp?url=/libr..._ga-gz_4z51.asp
RAND() – returns random value
http://msdn.microsoft.com/library/default.asp?url=/libr..._ra-rz_0mec.asp
DATEPART() – returns part of date/time
http://msdn.microsoft.com/library/default.asp?url=/libr...sqlcon_6lyk.asp
Hope it helps
_________________
Regards,
Nicole |
|
|
|
Graham
|
| Posted: 05/05/2005, 8:53 AM |
|
Nicole, You are a star. 
Replaced the last line with this:
RAND((DATEPART(s,GETDATE())*book_id))
Works like a charm.
|
|
|
|
|
