Randomly select rows in a table

Random Author: ZDNet Chinafriday, November 2003 4:08 PM

You can randomly select rows of data in a variety of ways; This is especially effective when you want to add dynamic features to your site. For example, you can select a product to display as "Today's featured Product", or a quality evaluator (QA) can generate a random call table to measure user satisfaction.

But the snag is that SQL does not allow random selection of columns. The good news is that there is a simple trick that can be implemented in SQL.

The solution is based on the uniqueidentifier data type. A unique identifier, also known as a globally unique identifier (guaranteed unique identifiers,guid), looks something like the following:

4c34aa46-2a5a-4f8c-897f-02354728c7b0

The SQL Server uses GUIDs in many cases, and the most obvious possibility is the replication process. You can use them when the normal Growth unit data column (identity column) does not provide enough keywords. To do this, you create a uniqueidentifier type of data column, and the default value for this type is newid (), just like the following:

CREATE TABLE mynewtable (PK uniqueidentifier not null DEFAULT NewID (), Anothercolumnvarchar (x) not NULL, ...

This function is like the key that solves our problem of choosing random columns. We can simply call NEWID () as a virtual column in our query, as follows:

SELECT top OrderID, NewID () as Randomfrom Ordersorder by Random

I recently created a page with 10 random user quotes to compliment the company's services, and I used this solution when I was doing this. Users can also see a new quote each time they visit the site.

This is an easy way to add new attraction points to your Web site. Now that you know how to publish randomly chosen data columns, you look like a SQL star.

The author of this article: Arthur Fuller has been developing database applications for more than 20 years. His professional experience includes Access ADPs, Microsoft SQL 2000, MySQL, and. NET.