[SOLVED] Random Select

[scottybwoy]

Just a quicky,

 

Is there a way to either select a random row from a column in an MS SQL table or from an array?  If so how would I go about it?

 

Thanks

[Barand]

SELECT acolumn FROM atable ORDER BY RAND() LIMIT 1

 

 

for arrays: www.php.net/array_rand

[scottybwoy]

Wow, glad there's something that simple, however LIMIT is not supported in MS SQL any alternatives?  Thanks

[Barand]

Sorry, didn't spot the "MS SQL".

 

SELECT TOP 1 acolumn ...

 

I'm not sure if MSSQL can do the ORDER BY RAND(). I don't have access to it right now

[Barand]

I did find this alterntive to ORDER BY RAND()

 

SELECT TOP 1 * FROM some_table AS r1

JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM some_table)) AS id) AS r2

WHERE r1.id >= r2.id

ORDER BY r1.id ASC

 

[scottybwoy]

Yes your right, however

 

SELECT TOP 1 modelId, purchasing, image FROM products AS r1

JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM products), 1) AS id) AS r2

WHERE r1.id >= r2.id

ORDER BY r1.id ASC

 

Returns an error of incorrect syntax near the keyword 'WHERE'.

 

Can you see what the error is, excuse my lack of knowledge on the complexity of this use of SQL, still a newb.

 

Thanks

 

By the way I added a 1 to Round()

[Barand]

I jusy copy'n'pasted that code from another site without checking.

 

Change WHERE to ON

[scottybwoy]

Got it!  I used :

 

SELECT TOP 1 modelId, purchasing, image FROM products AS r1

JOIN (SELECT ROUND(

RAND() * (

SELECT MAX(products.rrp) FROM products

), 1

) AS id

) AS r2 ON r1.rrp >= r2.id AND endOfLine != 'TRUE'

ORDER BY r1.rrp ASC

 

Problem was using a varchar as a handle wen it needed a float.

 

May I also ask where you source info like this?  Thanks for your help

[Barand]

I spotted that one in the notes for the array_rand link I gave you.