Questions tagged [random]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
-1
votes
2answers
39 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), ...
1
vote
1answer
20 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 ...
1
vote
1answer
28 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
1answer
53 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 ...
1
vote
1answer
29 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]) ...
0
votes
1answer
235 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
vote
1answer
59 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 ...
0
votes
1answer
27 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 ---------------------...
0
votes
1answer
49 views

PgAdmin Autogenerate random number

Hey guys right now 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? ...
1
vote
1answer
859 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 ...
3
votes
5answers
2k 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 ...
1
vote
2answers
1k 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 ...
-1
votes
2answers
177 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.
2
votes
2answers
79 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` ...
0
votes
1answer
2k 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 ...
0
votes
1answer
2k 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 ...
1
vote
2answers
836 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() ...
1
vote
3answers
58 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 ...
0
votes
1answer
36 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. ...
-1
votes
1answer
389 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 ...
2
votes
3answers
4k 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 ...
0
votes
1answer
235 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 ...
0
votes
1answer
75 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 ...
0
votes
2answers
861 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 ...
1
vote
2answers
9k 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
2answers
139 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 ...
3
votes
3answers
3k 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 ...
4
votes
1answer
1k 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 ...
6
votes
2answers
469 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
vote
2answers
3k 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 ...
0
votes
2answers
375 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 ...
3
votes
2answers
2k 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 ...
1
vote
2answers
38 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)] ...
3
votes
2answers
233 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 = ...
1
vote
1answer
2k 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 ...
4
votes
1answer
325 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 ...
2
votes
2answers
2k 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 ...
4
votes
1answer
1k 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 ...
5
votes
1answer
1k 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 ...
2
votes
2answers
6k 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 ...
4
votes
2answers
12k 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: ...
16
votes
3answers
11k 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. ...
2
votes
2answers
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 ...
3
votes
2answers
2k 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, @...
1
vote
1answer
708 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 ...
2
votes
1answer
132 views

Recommended Database(s) for Selecting Random Rows

I'm familiar with selecting random rows from a table in PostgreSQL. It's really slow. I've looked into some other databases and they all seem to be bad at it. Mostly because it's not a normal use case ...
0
votes
1answer
887 views

Result occurs often with fast random select query

I have this query to display 4 random photos on a website (I have it from here (when I asked for something faster than ORDER BY RAND())): (SELECT `p1`.`id`, `p1`.`url`, `p1`.`hits`, `p1`.`...
1
vote
1answer
1k views

What is the most efficient way to exclude records with a particular ID in a MySQL query for random records?

I have an api endpoint that returns X amount of random posts from a table called "posts". This endpoint is used by a mobile app. It retreives random posts by doing a SELECT COUNT(*) on the posts table ...
5
votes
2answers
2k views

How to Randomly Select a Date?

I am using in a Bash Shell Script this code for retrieving a random date. Is there a way to do that also in Microsoft SQL Server? I can imagine that through a single SELECT query and probably by ...
0
votes
0answers
123 views

Add Row To table on update Trigger

okay trying again. I have a trigger that I want to fire off whenever my order status changes to 'wc-processing'. So that My trigger doesnt copy ALL data onto this new table (vr_key) I want to use a ...