CODEWITHSUNDEEP
c#sql-servervisual-studiorandom
b0x0rz
b0x0rz

Reputation: 3981

Is there a SQL Server function for displaying pseudo-random database records?

is there a way to show a certain amount of random records from a database table, but heavily influenced by the date and time of creation.

for example:

is there such a thing in mssql directly? or is there some function (best practice) in c# i can use for this?

thnx

** edit: the title is really horrible, i know. please edit if you have a more descriptive one. thnx

Upvotes: 4

Views: 523

Answers (5)

Mau
Mau

Reputation: 14478

If you can filter results after DB access, or you can submit a query with order by and process results with a reader, then you can add a probabilistic bias to the selection. You see that the higher the bias, the harder the test inside the if, the more random the process.

var table = ...  // This is ordered with latest records first
int nItems = 10;  // Number of items you want
double bias = 0.5;  // The probabilistic bias: 0=deterministic (top nItems), 1=totally random
Random rand = new Random();

var results = new List<DataRow>();  // For example...

for(int i=0; i<table.Rows.Count && results.Count < nItems; i++) {
    if(rand.NextDouble() > bias)
        // Pick the current item probabilistically
        results.Add(table.Rows[i]);  // Or reader.Next()[...]
}

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294407

For a random sample, see Limiting Result Sets by Using TABLESAMPLE. Eg. Select a sample of 100 rows from a table:

SELECT FirstName, LastName
FROM Person.Person 
TABLESAMPLE (100 ROWS);

For weighted sample, with preference to most recent records (I missed this when first reading the question), then Martin's solution is better.

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453628

A quite simplistic way might be something like the following. Or at least it might give you a basis to start with.

WITH N AS
(
SELECT id,
       headline,
       created_date,
       POWER(ROW_NUMBER() OVER (ORDER BY created_date ASC),2) * /*row number squared*/
          ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [Weight] /*Random Number*/
  FROM news
  )
  SELECT TOP 10 
       id,
       headline,
       created_date FROM N 
  ORDER BY [Weight] DESC

Upvotes: 4

Michael Mior
Michael Mior

Reputation: 28752

Unfortunately I don't know MSSQL, but I can give a high-level suggestion.

  1. Get the date in UNIX time (or some other increasing integral representation)
  2. Divide this value by the max for each column to get a percentage.
  3. Get a random number and multiply by the percentage above.
  4. Sort your columns by this value and take the top N.

This will give more weight to the most recent results. If you want to adjust the relative frequency of older vs later results, you can apply an exponential or logarithmic function to the values before taking the ratio. If you're interested, let me know and I can provide more info.

Upvotes: 0

EFraim
EFraim

Reputation: 13028

You can pick an N using exponential distribution (for instance), and than SELECT TOP(N) ordered by date, and choose the last row. You can choose the exponent according to the number of existing rows.

Upvotes: 0

Related Questions