I can do two select queries:

1. select the top row customer_id
2. select all rows that match the above customer_id

But instead, I want to know if I can do the above in a single query?

I know it's possible using a subquery as in

SELECT * FROM customers
WHERE cust_id=(SELECT cust_id FROM customers LIMIT 1)

Is this an efficient way, or there is something better?

The requirement is to archive records that belong to a random cust_id or it might be correct for it to be the oldest cust_id (using aws lambda function in Python which runs periodically). Hence, I would like to fetch a cust_id and every other row with the same cust_id in a single transaction.

Using Postgres 10.5, with the DB table definition as below.
id BIGINT PRIMARY KEY, cust_id VARCHAR(100) NOT NULL, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, cust_data JSONB

CodePudding user response：

What you are doing is correct.

Just make sure to use ORDER BY to ensure that the cust_id is not random.

SELECT * FROM customers
WHERE cust_id=(SELECT cust_id FROM customers ORDER BY DATE LIMIT 1)

CodePudding user response：

Use the LIMIT and OFFSET clauses

SELECT * FROM customers
WHERE cust_id=(SELECT cust_id FROM customers LIMIT 1 OFFSET 0)

Or Use the MIN() function to get the minimum value of the cust_id column

SELECT * FROM customers
WHERE cust_id=(SELECT MIN(cust_id) FROM customers)