Complex Sql reference guides – posgres

1. Randomizing database table entries

WITH p1 AS (
    SELECT row_number() over (order by random()) n,
           salary AS salary1
    FROM people
),
p2 AS (
    SELECT row_number() over (order by random()) n,
           id AS id2
    FROM people
)
UPDATE people
SET salary = p1.salary1
FROM p1 join p2 on p1.n = p2.n
WHERE id = p2.id2;

2. Selecting a random row

Higher the multiplier higher is the chance of getting that record

select id, multiplier from items order by -log(1 - random())/multiplier::INTEGER LIMIT 1

2. Selecting distinct on

SELECT DISTINCT ON (url) url, request_duration FROM logs ORDER BY url, timestamp DESC

posgrescomplex sqltutorialexample

Latest Post

Information retrieval – Searching of text using Elasticsearch

Information retrieval is the process of obtaining relevant information resources from the collection of resources relevant to information need.

Learn more