Selecting a Random Row From a Database

Selecting a Random Row From a Database

by: stanleytan

This tutorial will demonstrate how to select at random a row from a table in a database. This can be very useful in many situations such as providing a "Quote of the Moment" or "Download of the Moment" type functionality. This implementation will use SQL Server with the bulk of the logic in a stored procedure. This tutorial will also show a concrete application of how to use this functionality through the demonstration of how to create a Donwload of the Moment application.

Adding the Functionality in a Stored Procedure

The stored procedure performs the bulk of the application logic. It is here that a random row from the database is selected. The application will then call the stored procedure which will return a random row.

1. Open Enterprise Manager.
2. Select the database you want to add this functionality to.
3. Right-click stored procedures and select "New Stored Procedure..."
4. Create the stored procedure. The following is an example of how to retrieve a random row from the downloads table:

CREATE PROCEDURE [dbo].[GetRandomDownload] AS
DECLARE @Rows int
SELECT @Rows = max(DownloadID) FROM Downloads
get_random:
SELECT DownloadID, Title, Description, Downloads FROM Downloads
    WHERE DownloadID = (SELECT CAST((RAND() * @Rows) AS int) + 1)
IF (@@rowcount = 0)
    GOTO get_random
GO

DownloadID is the primary key of the table. It is of data type int identity (auto-incrementing) and therefore the maximum number of rows is also the highest value for DownloadID. Hence, we assign this value into the variable @Rows. A random number is then generated with a maximum possible value of @Rows:

SELECT CAST((RAND() * @Rows) AS int + 1

The rand() function generates a random number between 0 and 1, which is multiplied to the @Rows variable. You may be wondering why 1 is added to the result. The reason is that the rand() function may return 0 while the DownloadID column starts at 1. Having generated a random number, we can then use the following SQL statement to select a single row from the Downloads table:

SELECT DownloadID, Title, Description, Downloads FROM Downloads
    WHERE DownloadID = (SELECT CAST((RAND() * @Rows) AS int) + 1)

Finally, we check the @@rowcount variable, which stores the number of rows affected by the last SQL statement. In this case, @@rowcount represents how many rows were returned in the previous SELECT statement. If the SELECT statement did not return any rows, then another random number would be generated until a row is selected. This is acheived by continually returning to the get_random label if no rows were selected. However, this case will only occur only if the auto-increment numbers are broken. For example, 5 rows are inserted into the database and one of the rows are deleted resulting in a gap between the auto-generated numbers. If rows are continually added to the Downloads table without deletion, there would be no problem as a second, third, or nth random number would never have to be generated.

Calling the GetRandomDownload Stored Procedure

The beauty of ADO.NET is that you can call stored procedures in the same manner as passing standard SQL to the database. This makes it very convenient for developers to work with.

SqlConnection conn = new SqlConnection("Data 
Source=localhost;Database=MyDatabase;Integrated Security=SSPI");
SqlCommand selectCommand = new SqlCommand("GetRandomDownload", conn);
conn.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
// do you stuff here
conn.Close();

First, we specify that we want to connect to the local SQL Server using Windows Authentication and open the database "MyDatabase", which should contain the Downloads table. Note that the connection is not yet open at this point in time. It is generally a good idea to open the connection to the database as late as possible and to close it as soon as possible to minimize the connected time to the database allowing for more scalable applications. Second, we specify that we want to call the "GetRandomDownload" stored procedure. We open the connection and use a DataReader to hold the results of the stored procedure. After this, we can use the results from the DataReader in our application. For instance, we can dispaly the name of the "Download of the Moment" in a Label and have a link to download that file. Lastly, we close the connection to the database.

The following is an example of the Download of the Moment application:

public class RandomRow : System.Web.UI.Page
{
    protected System.Web.UI.WebControls.Label Label1;

    private void Page_Load(object sender, System.EventArgs e)
    {
        SqlConnection conn = new SqlConnection("Data 
Source=localhost;Database=AcademicNet;Integrated Security=SSPI");
        SqlCommand selectCommand = new SqlCommand("GetRandomDownload", 
conn);
        conn.Open();
        SqlDataReader reader = selectCommand.ExecuteReader();
        while (true)
        {
            if (reader.Read())
            {
                string link = " a href='Download.aspx?DownloadID=" + 
reader["DownloadID"].ToString() + "'>" + reader["Title"].ToString() + 
"";
                this.Label1.Text = link;
                break;
            }
            else
            {
                reader.NextResult();
            }
        }
        conn.Close();
    }

    #region Web Form Designer generated code
    override protected void OnInit(EventArgs e)
    {
        InitializeComponent();
        base.OnInit(e);
    }

    /// 
    /// Required method for Designer support - do not modify
    /// the contents of this method with the code editor.
    /// 
    private void InitializeComponent()
    {
        this.Load += new System.EventHandler(this.Page_Load);
    }
    #endregion
}