Forum management
- Threads started by me
- Threads I participate in

Developer forum

SQL rnd() not working

Posted on 08/01/2010 10:21:26

Hi,

The following SQL sentence doesn't work in module "Databasepublicering" (dk):

select top 1 newsid, newsheading, newsmanchet, newsimage, newssmallimage, newslink from [news] where newscategoryid=11 and newsactive=true order by rnd(newsid)

When send directly to the database, it works fine. Anyone who knows how to make a simple SQL sentence like this work? The rnd(newsid) is used because the db is Access.

Best regards,
Anders

Replies

Posted on 04/02/2010 09:57:12

Hi Anders

I tested your sql statement on my local access solution, and it seems to work fine (of course, I removed the clause on category id).
What is the error exactly?

- Lasse

Posted on 04/02/2010 12:55:50

Hi Lasse,

Actually, there's no error except the same news item is shown each time I reload the page.

I've submitted a support case again and asked the supporter to forward it to you.

Posted on 05/02/2010 09:55:41

Hi Anders

The problem is not a Dynamicweb bug - the SQL statement you apply is parsed to Access directly.

Check out these documents on the rnd function:
http://office.microsoft.com/en-us/access/HA012289011033.aspx
http://support.microsoft.com/kb/287682

I think the solution is to use a negative number as your seed to the rnd function (e.g. using -1 * <id>)

- Lasse

Posted on 05/02/2010 11:10:37

Ok, I tried with -1 but it didn't work at all. Maybe I didn't constructed the sentence correct.

I googled this, which worked:

select top 1 newsid, newsheading, newsmanchet, newsimage, newssmallimage, newslink from [news] where newscategoryid=11 and newsactive=true order by rnd(timevalue(now())*-10000000*[newsid])

You must be logged in to post in the forum