How to select a random row from a SQL database table?

Question

I am attempting to retrieve a random row in my table from Visual Studio's database. However, it is unable to retrieve a random row and throws this specific exception:

System.Data.SqlClient.SqlException: 'Incorrect syntax near 'LIMIT'.'

This is my function code to retrieve a random table row:

Voucher randomVoucher = null;
int voucherID;
string voucherName, voucherDescription, discountCode;
decimal discountAmount;

string queryStr = "SELECT * FROM Voucher ORDER BY RAND() LIMIT 1; ";

SqlConnection conn = new SqlConnection(_connStr);
SqlCommand cmd = new SqlCommand(queryStr, conn);

conn.Open();
SqlDataReader dr = cmd.ExecuteReader();

if (dr.Read())
{
    voucherID = int.Parse(dr["VoucherID"].ToString());
    voucherName = dr["VoucherName"].ToString();
    voucherDescription = dr["VoucherDescription"].ToString();
    discountAmount = decimal.Parse(dr["DiscountAmount"].ToString());
    discountCode = dr["DiscountCode"].ToString();

    randomVoucher = new Voucher(voucherID, voucherName, voucherDescription, discountAmount, discountCode);
}
else
{
    randomVoucher = null;
}

conn.Close();
dr.Close();
dr.Dispose();

return randomVoucher;

What is _"Visual Studio's database"_? Do you mean SQL Server Express? — ProgrammingLlama, Feb 08 '21 at 07:36
SQL Server doesn't use `LIMIT`, it uses `SELECT TOP 1 ...` instead, different syntax. — Lasse V. Karlsen, Feb 08 '21 at 07:37
Is `RAND()` really a valid argument for `ORDER BY`? I would considering just doing it in two queries, a count, creating a random number in c# and then [getting that specific row](https://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table). — JonasH, Feb 08 '21 at 07:41
change the query to `SELECT TOP 1 * FROM Voucher ORDER BY NEWID()` — Squirrel, Feb 08 '21 at 07:54
Apart from the error in using LIMIT instead of TOP (this is what the duplicates tells you) the correct way to get a random order in sql server is ORDER BY NEWID() — Steve, Feb 08 '21 at 07:55

Rezaeimh7 · Answer 1 · 2021-02-08T08:08:45.647

0

Limit is a Mysql statement and one equivalent for it in Sql Server can be using OFFSET and FETCH

string queryStr = "SELECT* FROM Voucher ORDER BY newid()  OFFSET 10 rows FETCH NEXT 10 ROWS ONLY;";

It reads 10 rows of a randomly sorted database starting from the 10th row.

edited Feb 08 '21 at 08:08

answered Feb 08 '21 at 07:45

Rezaeimh7

1,467
2
23
40

1

Read the docs: _Returns a pseudo-random float value from 0 through 1, exclusive._ – Steve Feb 08 '21 at 07:48

score 0 · Answer 2 · answered Feb 08 '21 at 07:52

You can get random number from range of 1 to your table records count then select that one from table and get the result!

pseudo code:

// This is like a entity framework sample,
// You can get it from query by using SQL COUNT => SELECT COUNT(*) FROM $TABLE_NAME$;
tableRecordCount = table.Count();
//generating random number more then 0 and less then tableRecordCount
int randomNum = random.Next(1, tableRecordCount); 
//Then getting row with this id;
//Or with query => SELECT * FROM $TABLE_NAME$ WHERE id = randomNum ...
var randomRow = table.firstOrDefault(r => r.id == randomNum);

So if "id" is your "Primary key" column and the values are unique you will get one record with id that is our generated random number! Good luck!

While this could work, it requires two queries against the database or downloading the entire table in memory — Steve, Feb 08 '21 at 07:56
This seems like it will work, just needs to check if the random ID actually exists in the database and has not been delete, if not pick a random ID again. — kahhao39, Feb 08 '21 at 10:34

How to select a random row from a SQL database table?

2 Answers2