Questions tagged [random]
The random tag has no usage guidance.
72 questions
0
votes
1
answer
36
views
Sleeping for a random amount of time in a stored procedure
A vendor application we're using utilizes multiple processes (not threads) spread across multiple computers, one for each processing core available. When asked for a batch of calculations, the ...
- 136
2
votes
2
answers
558
views
How to update a foreign key with random values from the referenced table?
In order to generate test data for a Postgres database, I want to update a foreign key in a table, let's say 'users' with random values from the referenced table, let's say 'shift'.
How to do it?
I ...
3
votes
1
answer
887
views
Set seed for multiple random numbers in postgres
The following will generate the same random number:
SELECT
setseed(0.5),
generate_series(1, 3) as id,
round(random()::decimal, 2) as random_number;
Which outputs something along the lines of :
...
- 316
2
votes
2
answers
1k
views
Obtain different random() values with generate_series() in PostgreSQL
I am trying to generate random data (in volume) and was trying to use PostgreSQL's random() function with generate_series(), as shown:
SELECT
(SELECT random()) AS a,
(SELECT random()) AS b,
FROM ...
- 135
0
votes
1
answer
974
views
Randomly generate data under conditions for sqlite
As part of creating a machine learning exercise, I have an sqlite database that looks like this:
The "Pizza", "DateDimension" and "Customer" tables are already populated,...
-1
votes
2
answers
564
views
A quick way to ORDER all IDs randomly in MySQL [closed]
I need to assign random IDs to a table. Thus, I create a mapping table as
CREATE TABLE t2
(
ID int(11) unsigned NOT NULL AUTO_INCREMENT,
SourceID int(11) unsigned NOT NULL,
UNIQUE INDEX(SourceID),
...
- 4,531
1
vote
1
answer
668
views
In Teradata SQL how to get random number between a pair, coming from field/column?
I am recently got stuck upon a simple problem in Teradata: how to get random number between a pair of number coming from field/column?
The function RANDOM( lower_bound , upper_bound ) apparently is ...
- 219
1
vote
1
answer
1k
views
UPDATE random rows
I'm working on creating a more efficient way to UPDATE random rows in a ticket allocation website we have.
Currently, we pre populate the tickets table with ticket numbers in subsequent order, i.e 1,2,...
-2
votes
1
answer
697
views
Do databases optimize random write and read operations? [closed]
Random write/read is slower than sequential for HDDs and some SSDs. Some database operations (UPDATE .. WHERE ..) may cause many random writes/reads. So I want to know if modern databases implement ...
- 233
2
votes
1
answer
511
views
What is the best way to divide data for random groups?
I would like to have persisted column which will randomly group data for 32 groups according to one of varchar key column in table. My idea was:
SELECT ABS(CAST(HASHBYTES('MD5',[keyColumnFromTable]) ...
- 119
1
vote
1
answer
2k
views
Generate random row order in a subquery
I know other answers here (and here) say to order by newid(). However if I am selecting top 1 in a subquery - so as to generate a random selection per row in the outer query - using newid() yields the ...
- 1,645
2
votes
1
answer
2k
views
Duplicating rows based on a count column SQL Server [duplicate]
I have the following table:
MyTable
PlaceID NameID Code CodeCount
1 1 1234A 2
1 2 1234A 3
1 3 1234A 1
2...
2...
3...
3...
I want to select 1234A where ...
- 167
0
votes
1
answer
710
views
MySQL order by rand but result must exist different specific value
I'm trying the get random values from SQL but I want results must have minimum one specific value from one column, how can I do it?
For example;
TABLE;
id | category | name
---------------------...
- 13
1
vote
1
answer
1k
views
How to auto-generate unique random-looking numbers?
Currently I'm passing one value to my database, it's a random and unique 10-digit number.
Is there a way I could generate this number directly in my database each time a new row is created?
It looks ...
- 11
5
votes
1
answer
5k
views
order by random meaning (postgresql)
One possible way to select random rows in PostgreSQL is this:
select * from table order by random() limit 1000;
(see also here.)
My question is, what does order by random() mean exactly? Is it that ...
- 151
9
votes
5
answers
10k
views
Get a truly RANDOM row from a PostgreSQL table quickly
I always used to do:
SELECT column FROM table ORDER BY random() LIMIT 1;
For large tables, this was unbearably, impossibly slow, to the point of being useless in practice. That's why I started ...
user200642
7
votes
2
answers
7k
views
Perform TABLESAMPLE with WHERE clause in PostgreSQL
I want to use TABLESAMPLE to randomly sample from rows that meet a certain condition with PostgreSQL.
This runs fine:
select * from customers tablesample bernoulli (1);
But I cannot figure out how ...
- 179
-1
votes
2
answers
906
views
Generating a Random Account Number
For privacy purposes, I need to derive an "Account Number" field that contains a random number, ranging from 1000 to 2000.
- 189
2
votes
2
answers
222
views
Select random database records between subset of records having the same count(*) in a query
I have this simple query:
SELECT `value`, count(*) as `noHits`
FROM `topic`
WHERE identifier IN ('" . implode("','", array_keys($processedIdentifier)) ."')
GROUP BY `value`
...
- 21
1
vote
1
answer
9k
views
Generate unique random string to be used as value for a field inside newly created rows in PostgreSQL
I have this statement which generate a random string:
SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '')
FROM ...
- 167
1
vote
1
answer
4k
views
How can I create a unique and random alphanumeric string using MySQL's Default Value? (v 5.6)
I am building a MySQL database and think using implicit DEFAULTS seems very promising.
I am successfully using DEFAULT CURRENT_TIMESTAMP and DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP for ...
- 111
2
votes
2
answers
4k
views
postgresql random function returns duplicate results when order by random used in query
I'm using postgresql 11.1-1 on Windows 10. On my system, if I run a query like the following, I will get a different number for each random column:
postgres=# SELECT random() as sortOrder, random() ...
- 133
2
votes
3
answers
508
views
PSQL: create random data sets with the same statistic distrbution as another table
For test purposes, I want to create data sets (tables) where the statistic distribution of the values in the columns is the same as my actual data, but randomized.
Example : consider the following ...
- 311
0
votes
1
answer
140
views
Stored Proc Logical Reads Spiking Daily At The Same Time [closed]
I have a procedure that I optimized in a dev environment. It is a heavy lifter and started at 14 millions logical reads with a run time of 30-40 seconds. I got it down to 96k reads and 8 seconds. ...
- 61
-1
votes
1
answer
560
views
Randomly select 4 items of a randomly selected category
I need an idea how to construct this query.
I have a web page. each time the page is refreshed, I want to select data from a random category, and to select no more than 4 random items from that ...
- 1
2
votes
3
answers
9k
views
how to replace user's names with random names?
How can I use SQL only on mysql DB to replace first and last names of real users in users table using a random name from two related tables: random_first_names and random_last_names. Our users table ...
- 131
0
votes
1
answer
2k
views
SQL TOP statement, how to make sure it's "random"?
I was studying SQL where I encountered a TOP statement. i.e.
SELECT TOP 2 * FROM Persons
select at most 2 row from table Persons.
However, if I were to use it for statistical reason, I'd like such ...
- 103
0
votes
1
answer
171
views
Why is random password generation the same when it's inside while loop? TSQL
I have a random password generator. It works fine by itself. When I put it inside of a while loop, I get the same password even though the seed appears to be different.
declare @count int = 0
...
- 135
0
votes
2
answers
1k
views
Fetch one value from random row where value is not x
I have a games table in my db, which has id column(pk) and slug column(unique) - other columns are not relevant. Now, what I'd like to get is one row, or it's slug value only, which would be random ...
- 103
1
vote
2
answers
10k
views
how to select random and unique records from mysql (in php language)
I have a question table in my database which consists the following columns
question number which ranges from 1 to 150.
question text
text for 4 options as A,B,C,and D
answer option(either a,b,c or ...
3
votes
2
answers
288
views
Detect if `pg_crypto` in Postgres was built with strong random feature enabled
The source code for pg_crypto module mentions an option to build without cryptographically-strong random generation.
/*
Generate random bits. pg_backend_random() will do here, we don't promis
UUIDs ...
- 11k
3
votes
3
answers
4k
views
How can I get satisfactory random numbers from the RAND function (or elsewhere)?
I'm creating a pseudo-random data set for application users to train on.
I'm surprised that if I seed the RAND() function with 1, 2, 3, etc., I get very nearly the same result from the seeded ...
- 750
5
votes
1
answer
2k
views
May postgresql's uuid_generate_v1() conflict when we change the computer date/time?
According to postgresql uuid-ossp documentation uuid_generate_v1() is based on Mac address + timestamp:
https://www.postgresql.org/docs/9.4/static/uuid-ossp.html
On a distributed database scenario ...
- 467
7
votes
2
answers
745
views
PostgreSQL randomising combinations with LATERAL
In the following example I have a table foo from which I'd like to pick out at random a row per group.
CREATE TABLE foo (
line INT
);
INSERT INTO foo (line)
SELECT generate_series(0, 999, 1);
Let'...
- 1,730
1
vote
2
answers
4k
views
How to select random row with count()
Table names:
g_number name
123 John
123 Sara
123 Michael
I want to get one RANDOM name and quantity of all names with the same g_number in one query:
SELECT name, count(*) cnt
...
- 121
0
votes
2
answers
532
views
PWDENCRYPT in user-defined function
In writing a random number function, I have learned that you can’t use NEWID() or RAND() inside a user function because they are non-deterministic.
I have also discovered that I can use PWDENCRYPT ...
- 2,919
3
votes
2
answers
3k
views
How to (optimally) get random sample of (a.id, b.id) pairs from two tables (a, b)?
Let's assume I have to very simple tables
CREATE TABLE a(id integer PRIMARY KEY,
t timestamp default now(),
sensor_readings real[]);
CREATE TABLE b(id integer PRIMARY KEY,
t ...
- 13.6k
1
vote
2
answers
84
views
How we can design best optimized solution: Generate huge random numbers and check in database table whether this numbers are there or not
We have one table like:
Table Name:
random_number_series
Its columns are as below:
number [numeric(10)]
name [varchar(10)]
status [tinyint(1)]
type [tinyint(1)]
...
- 107
4
votes
2
answers
343
views
How did this statement update 3 rows in Postgres?
I am curious how this statement updated 3 rows in Postgres. All the other times I ran it, it would update 0 or 1. Is there a way to find out which rows?
bestsales=# update keyword set revenue = ...
- 226
1
vote
1
answer
3k
views
How Can Get Random 2 rows FROM EACH GROUP FROM JOINED TABLE
EXAMPLE
Table Dept:
dept_id dept_name dept_status
---------------------------------------
1 Sales 1
2 Develpment 1
3 HR 1
...
- 13
4
votes
1
answer
536
views
Optimizing "random" choice in PostgreSQL
I have the following table
CREATE TABLE article (
id bigserial PRIMARY KEY,
text TEXT NOT NULL,
category_id bigint REFERENCES categories (id),
status TEXT NOT NULL,
locale TEXT ...
- 251
2
votes
2
answers
4k
views
Update function to enter random/unique GUID per row
I am new to SQL and am trying to do the following.
i am using SQLite browser to edit the current file I have
I do have a SQLite database with around 1500 rows of data that I would like to create a ...
- 21
4
votes
1
answer
2k
views
Oracle random number as default column value
I'm trying to create a table in OracleDB and assign random number to one of the columns as a default. I have this setup working in postgresql
create table table_name (
column integer NOT NULL ...
- 41
5
votes
1
answer
2k
views
Generate fake data for several tables with relationships
I have 3 tables where I want to generate fake data in UsersCategoryLink.
How can I insert in the table UserCategoryLink the column UserID with random users from the table User and a random id from ...
- 930
2
votes
2
answers
7k
views
fill table A with random ID's from another table B
I have two tables and I want update the column GebruikerID from table Topic with the ID of the table Gebruiker with random values.
You must also know the follow things:
Topic.GebruikerID references ...
- 930
4
votes
2
answers
13k
views
make random numbers for each row and column
I will edit some fake data in my database. But if I will make random numbers for each row and column, it is not so random as I want it. The result can you see on image below.
Here is my code I use:
...
- 930
16
votes
3
answers
13k
views
Create hierarchy of multiple levels where each node has a random number of children
I need to create some test data that involves a hierarchy. I could make it easy and do a couple of CROSS JOINs, but that would give me a structure that is completely uniform / without any variation. ...
- 69.5k
2
votes
2
answers
1k
views
Selecting random rows from database initially while preserving that order later
I am building a quiz app, in which the student can login and can select the option of give exam. I want to show multiple choice questions to students randomly.
I stored all the questions in one ...
- 31
3
votes
2
answers
3k
views
Use random string variable in a insert
This is one of my first tries to code something. I'm trying to develop a code, to add first name, last name, and date, randomly.
I have this for now:
declare @date date,
@val1 int,
@...
- 7,484
1
vote
1
answer
804
views
mysql - select random rows with max 3 rows per parent id
I currently have a query that returns a maximum of 3 rows per parent_id. I want to enhance the query to return a maximum of 3 random rows per parent_id. This does not work for me at the moment as it ...
- 111