Postgres SQL - MAX criteria in WHERE - beginner problem

Question

I'm taking beginner postgres online course and below is the question and sample answer. But I'm wondering if I can use MAX instead of ORDER BY DESC?

---

Question:

What customer has the highest customer ID number whose name starts with an 'E' and has an address ID lower than 500?

The suggested answer is :

 SELECT first_name,last_name FROM customer
 WHERE first_name LIKE 'E%'
 AND address_id < 500
 ORDER BY customer_id DESC LIMIT 1

I tried the code below but it returns to nothing. Did I miss anything else? I tried the same concept for other similar queries and it worked. I just don't understand why this one doesn't work. There should be one row affected.

 SELECT first_name, last_name, customer_id FROM customer
 WHERE first_name LIKE 'E%'
 AND address_id < 500
 AND customer_id = (SELECT MAX(customer_id) FROM customer)

Thanks in advance! EDIT 1: I wrote MIN in my attempt. It should be MAX, anyway even if I changed, it still returned to nothing.

EDIT 2: Below I added the detail on the "similar query".

Below code works, it basically returns to all the movie with rating of R or NC-17, length more than 55, where the rental rate is the cheapest(minimum).

SELECT title, length, rating, rental_rate FROM film
WHERE rating IN('R','NC-17')
AND length <=55 
AND rental_rate = (SELECT MIN(rental_rate) FROM film)

Edit 3: So I thought well, my code above works, so it should work in solving the new query of finding the MAX customer_id. This is supposed to be simple. I feel realy itchy to know why my code with MAX doesn't work. Please help me.

Let's say that there are 3 rows of data to analyse, see below.

CREATE TABLE customer (
first_name VARCHAR(50),
last_name VARCHAR(50),
address_id INT,
customer_id INT
)

INSERT INTO customer (first_name, last_name,address_id,customer_id )
VALUES 
(Eddie, Longbottom, 501, 100),
(Freddie, Longbottom, 501, 101),
(Ellie, Longbottom, 502,99);

The answer should return

Eddie Longbottom 501 100.

But I just wanted to use MAX rather than ORDER BY customer_id DESC.

Please help..! TYSM

Answer 1

Ok.... It's Solved...

SELECT first_name, last_name, customer_id, address_id FROM customer
    WHERE customer_id= (SELECT MAX(customer_id) FROM customer 
                        WHERE first_name LIKE 'E%' AND address_id <500)

Edit. My other query with MIN- it worked by chance or specifically because when you get the MIN rental_price there are more than one answer. But it's not the right way.

I'm going to definitely request refund for this course!!!!!!!!!!!

Answer 2

That's clearly not going to work (and we have no idea what a "similar" query is unless you show it).

Imagine just two rows

customer_id	Name
3	Doris
5	Edith

Your main query matches only Id 5, your sub query matches only Id 3.

Row 3 and 5 are not the same = no row qualifies. You can't split the filtering criteria across two uncorrelated queries.

You would need

select first_name, last_name, customer_id
from customer 
where customer_id = (
  select min(customer_id)
  from customer
  where address_id < 500 and first_name like 'E%'
);

However the answer you have is a better solution.