A friend of mine recently asked me how to select random rows from a table using a SQL query which let me to find a solution. If you are wondering why we need to select rows randomly from a table, consider a simple example of an online quiz which needs to display the questions in a random order for each person who is going to take the exam.
After a bit of research and googling I found the answer and then thought of sharing it here so that I can refer it at a later point of time and at the same time it may be useful for others who are searching for a similar solution.
In Oracle we have to use the sys_guid() function. The query will look like
SELECT col1, col2 FROM tblname ORDER BY SYS_GUID()
In SQL Server the query would look like
SELECT col1, col2 FROM tblname ORDER BY NEWID()
In MySQL the query would look like (Thanks Aswin)
SELECT col1, col2 FROM tblname ORDER BY RAND()
If any of you know how to do this in other databases or have a better solution than this, leave it the comments so that I can mention them here.
In MySQL you can do the same like
SELECT col1, col2 FROM tblname ORDER BY RAND()
Hi Aswin,
Will include this one also.
Cheers,
Sudar