Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [random]

The tag has no usage guidance.

1
vote
2answers
42 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
49 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
34 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
220 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
vote
3answers
546 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
78 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
53 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
517 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
6k 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
74 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 ...
3
votes
3answers
2k 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
606 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
304 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
2k 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
199 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
861 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
25 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
187 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
1k 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
232 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
964 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
688 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
4k 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
9k 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
9k 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
924 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 ...
2
votes
2answers
1k 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
628 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
110 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
792 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
746 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
1k 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
122 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 ...
3
votes
2answers
3k views

Sampling in PostgreSQL

I am looking for possible ways of random sampling in PostgreSQL. I found a couple of methods to do that with different advantages and disadvantages. The naive way to do that is: select * from ...
1
vote
1answer
966 views

How to generate a random character string of length 8 in Netezza?

I am trying to generate a random character string of length 8 in Netezza, any suggestions?
1
vote
2answers
1k views

Distinct Random select

Hi can anyone help with this little MS SQL Puzzle. I basically need to get a 1 random answer from each of the submitters. id, submitter, answer 1, James, Blue 2, James, Red ...
0
votes
2answers
79 views

SQL Server choose at random from giving data

My problem is that I don't know how to choose the random data from a giving set. For example: I have a Node column which need to produce different inserts but only from these numbers: 16, 17, 18, 22, ...
3
votes
3answers
5k views

Select random 10 unique rows from MySQL database?

I have one table (questions) with 54 questions. The students have to answer all 54 questions. I have to show 10 questions at once, then next 10 and so on.... I have to select 10 questions ...
0
votes
0answers
225 views

MYSQL : Copying to TMP table nightmare

I'm having a (AFAIK) random issue. My server is MySQL and I have a query that is sometimes 10000 times slower as you can see on this screensot : http://snag.gy/vJJ39.jpg The long time occurs during 2 ...
1
vote
2answers
7k views

Postgresql. Generate sets of random integers

With this command I can generate 16384 random integers between 1 AND 200,000. SELECT generate_series (1,16384),(random()*200000)::int AS id I want to generate 10 sets of such integers. Each set must ...
7
votes
1answer
8k views

Randomizing the Auto Increment in Mysql

We have a database, where we insert data into table whenever we receive an order from our services. We use auto_increment to generate the ID and assign the order with the same. This is usually same ...
1
vote
2answers
843 views

Perform group by with random group expression (randomly partitioning rows) MYSQL

Let's say I have a table (with millions of rows) cat1|cat2|value A | B| 10 ... and I want to group the data by cat1 and also split the grouping into 10 (at least roughly) equal partitions of ...
2
votes
3answers
2k views

Primary key with randomly varying increments (so it cannot be guessed easily)

I would like the primary keys to be auto-incremented and generated but in varying increments. For ex, if I have increment range as 100... then the auto generated keys would be something like below: - ...
2
votes
1answer
1k views

Create a new table (multiple columns) by selecting random values from an existing table

I am stuck on the following task (PostgreSQL 9.3). Let's say we have the following table1 (which has 10k rows): table1: id 754 800 330 4 59 My goal is to create another table2 with two columns (...
7
votes
3answers
8k views

Set random value from set

I need to put some random values into database, but I don't want to end up with completely randomized text (like 7hfg43d3). Instead I would like to randomly pick one of values supplied by myself.
0
votes
1answer
1k views

Getting MySQL User Variable in Sequence, While Using ORDER BY RAND()

I am running a SELECT statement, which ultimately will be in an INSERT..SELECT statement. I want a result like this ID COUNTER 3 1 7 2 1 3 20 4 12 ...
0
votes
1answer
753 views

MySQL non-sequential primary key of certain length

what's the best way to create a non-sequential primary key of a certain length in MySQL? I'm looking to use the primary key as a 'Pin' number which real actual human people can use to refer to the ...
16
votes
2answers
3k views

How can I generate a random bytea

I would like to be able to generate random bytea fields of arbitrary length (<1Gb) for populating test data. What is the best way of doing this?
9
votes
2answers
13k views

How to create column in DB with default value random string

Can I create column in DB table (PostgreSQL) which have default value random string, and how ? If is not possible, please let me know that.