Getting A Random Row From A MS SQL Table

9th January 2008 - 1 minute read time

Note: This post is over a year old and so the information contained here might be out of date. If you do spot something please leave a comment and we will endeavour to correct.

To get a random row from a Microsoft SQL database you need to use the NEWID() function. This will generate a new random number for each row and order them by that new number. This is used in conjunction with the TOP clause to limit the amount of returned rows to one.

SELECT TOP 1 value FROM table ORDER BY NEWID()

random

SQL

Add new comment

Related Content

Seeding Random Numbers in PHP

Computers are not that great at creating random numbers as the methods they use are deterministic. That is, they start from a number (called a seed) and apply maths to that number to generate pseudorandom numbers. Most random numbers generated by computers programs use a seed provided by the system, which is generally the current time. If you can guess the initial value then you can start to work out what the random sequence of numbers generated is. Most random numbers, are therefore not suitable for encryption.

Case Insensitive Like Searches In MySQL

I needed to create a query that did a case insensitive search using the LIKE command in MySQL and I quickly realised that in order to do this I would need to alter both the parameter and the table data to be the same case. This can be done by using the MySQL UPPER() command on the table data and the strtoupper() PHP function on the input data.

$name = strtoupper('phil');
$query = "SELECT * FROM users WHERE UPPER(forename) LIKE '" . $name . "%'";

This will produce the following SQL query.

Round A Number In MS SQL

To round a number in MS SQL use the ROUND() function. This function takes two parameters, the first is the number to be rounded and the second is the number of decimal places to round the number to. Here is an example of rounding the numbers in a column to the nearest whole integer.

Concatenate Strings In MS SQL

If you want to concatenate the output of three different columns in MS SQL just use the + symbol.

Limit Number Of Rows Returned In MS SQL

To limit the number of rows returned in a MS SQL query you need to use the TOP command. This goes before you name the columns that are to be returned by the SELECT statement.

The following query returns the first 35 rows from a table.