CODEWITHSUNDEEP
sqlpostgresql
Cameron
Cameron

Reputation: 37

Selecting a random row from a Postgres table using multiple AND statements

I have a Postgres table set up using this schema

CREATE TABLE techwear
(
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    style techwearStyles,
    article clothingType,
    color techwearColors,
    price DECIMAL(6,2),
    link VARCHAR(500),
    image VARCHAR(250)
);

Currently, I have a statement that is capable of selecting a random row by using an AND statements:

SELECT * FROM techwear
WHERE (style='urban' AND color='black')
OFFSET floor(random() * (SELECT COUNT(*) FROM techwear))
LIMIT 1;

However, when I introduce a second AND statement:

SELECT * FROM techwear
WHERE (style='urban' AND color='black' AND article='top')
OFFSET floor(random() * (SELECT COUNT(*) FROM techwear))
LIMIT 1;

I get 0 rows returned to me. However, I know for a fact there are rows that satisfy the WHERE statement completely. My only guess is that there is something wrong with the OFFSET statement. I'm using this method instead of ORDER BY because I've been told it's much faster.

Does anyone have any idea on what might be causing my issue?

Upvotes: 1

Views: 74

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

Problem with your query is, you are filtering your records with where clause but you are taking full count of your records in offset. Due to that offset value can be greater than number of records returned by your query. Therefore it will not return any record.

SELECT * FROM techwear
WHERE (style='urban' AND color='black' AND article='top')
OFFSET floor(random() * (SELECT COUNT(*) FROM techwear 
                         WHERE (style='urban' AND color='black' AND 
                         article='top')))
LIMIT 1;

But better you should write your query by using order by random() like this:

SELECT * FROM techwear
WHERE (style='urban' AND color='black' AND article='top')
order by random()
limit 1

No need to write sub queries for this

Upvotes: 2

Related Questions