Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage


  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats

    • 7,324,495 Views

Random Sorts

Posted by decipherinfosys on February 25, 2007

In one of the previous blog post, we had talked about random number generation in Oracle, SQL Server and DB2 LUW. Let’s now look at how to do random ordering of the data. This becomes a requirements at times when in a lottery system random ordering is necessary as well as in systems like a testing system where no two tests should be rendering the same set of questions. Here is how you can go about doing it – these examples assume that you are interested in the top 1 record that is returned back from the random sorting operation – real world queries will have more than one table and additional filter criteria, but these examples over here are being used just to illustrate the point:

Oracle:

SELECT * FROM (SELECT * FROM TABLE_X ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM < 2

SQL Server:

SELECT TOP 1 * FROM TABLE_X ORDER BY NEWID();

DB2 LUW:

SELECT * FROM TABLE_X ORDER BY RAND() FETCH FIRST 1 ROWS ONLY;

Sorry, the comment form is closed at this time.

 
%d bloggers like this: