Question 247005 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres SQL – MAX criteria in WHERE – beginner problem

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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!!!!!!!!!!!


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search