Posts tagged with "MySQL"

Row numbers within MySQL

Posted within Development on by Ryan Ball

Sometime you will need a query that will return a resultset of ranked records. The following MySQL query will do the trick:

SELECT @rownum:=@rownum+1 AS rank, companies.* FROM conpanies p, (SELECT @rownum:=0) r ORDER BY profit DESC LIMIT 20;

Random Records

Posted within Development on by Ryan Ball

Just adding these code snippets as a reminder for future projects.

MySQL
SELECT someColumn FROM someTable ORDER BY RAND() LIMIT 1

MS SQL Server 7.0 and above (running on Windows 2000)
SELECT TOP 1 someColumn FROM someTable ORDER BY NEWID()

MS SQL Server 7.0 and above (not running on Windows 2000) 
SELECT TOP 1 someColumn FROM someTable ORDER BY RAND((1000*IDColumn)*DATEPART(millisecond, GETDATE()))