Jump to content

[SOLVED] Need to Limit Rand() QueryString Results on SQL2000


selliott

Recommended Posts

First, here's my currently functioning code:

 

$QueryString="SELECT Top 4 * FROM ads WHERE Terminate > GETDATE() ORDER BY RAND()";

 

Now, this limits my results to 4...but it's only showing the same Top 4 every time...and it doesn't appear to be randomizing. If I refresh the xml.php page, I keep seeing the same nodes in the same order.  The big issue is, I don't want the Top 4...I want 4 random.  What I want this to do is give me 4 Random ads from ALL the ads that haven't expired (have a terminate date > now).

 

I'm guessing something like this would work perfect, if my SQL server recognized LIMIT

 

"SELECT * FROM ads WHERE Terminate > GETDATE() ORDER BY RAND() LIMIT 4";

 

Link to comment
Share on other sites

After 10 seconds googling, mssql doesn't support the limit clause. You'll need to use a derived table:

 

SELECT Top 4 * FROM (Select * FROM ads WHERE Terminate > GETDATE() ORDER BY RAND() AS subtable)

 

Never having worked with ms sql, i can't guarantee you'll need that alias at the end (or rather, that it'll work with it) - but you would do in mysql, so give it a whirl.

 

Next time, you should post this question in the mssql board: http://www.phpfreaks.com/forums/index.php/board,35.0.html

Link to comment
Share on other sites

Yeah, I knew the LIMIT command wouldn't work (after some googling before my post), I just didn't know what other options I had to accomplish the same thing on mssql.

 

I tried your code idea and did have to remove the AS subtable (got error) and now it doesn't seem to like the last ")" for some reason.  The error reads: Incorrect syntax near ')'

Link to comment
Share on other sites

I made a little progress on this after some trial and error, some more googling and a few minor adjustments.

 

SELECT Top 4 * FROM (Select TOP 1000 * FROM ads WHERE Terminate > GETDATE() ORDER BY RAND()) AS newtbl

 

But I'm still getting the same 4 results?

Link to comment
Share on other sites

I'm going to email my host and see what they say about this.  It's acting like the rand() just isn't working.  Like it collects the top 1000 without randomizing, then the top 4 from that...which gives me the same top 4 every time...in the same order.  I'll post in here after I hear back from them.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.