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

[selliott]

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";

 

[GingerRobot]

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

[selliott]

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 ')'

[gevans]

can you post the exact code you're using, an error is no good without the code to see

[selliott]

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?

[selliott]

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.

[selliott]

I ended up using ORDER BY NEWID() instead of ORDER BY RAND() and it works now.