How to filter results based on a MAX()/MIN() value

[lucascv]

Hi, folks!

I’d like some help with an issue I’m facing in both a project and I found the same in a exercise.

In the MET Museum exercise (https://www.codecademy.com/paths/data-science/tracks/sql-intermediate/modules/dspath-sql-aggregates-projects-and-applications/projects/sql-the-met), task 4, we have to " Find the title and medium of the oldest piece(s) in the collection."

However, the shown solution involves a double step solution, first finding the oldest date, then using LIKE to search. But I’m wondering, how would a real-life implementation of this work?

I tried this:

SELECT title, medium
FROM met
GROUP BY year
HAVING year = MAX(year);

The same issue I’m having in Data Science Ind. Project #4 (Data Science Independent Project #4 – Home Value Trends), when in I have to find max/low price of the given last month, I tried a similar approach to no avail also.

So, what am I doing wrong? How would be this implemented?

Thanks in advance for the help!

[el_cocodrilo]

@lucascv,

I am not going to give you exact code, since it looks like you may be using this in a project to demonstrate your SQL skills.

That being said, I’ll guide you in the right direction.

First, you may want to start by ensuring your test queries use the correct column names. In the Met project, the column is date, not year, so that is where your troubles may have started.

@lucascv

SELECT title, medium
FROM met
GROUP BY year
HAVING year = MAX(year);

Next, Google is your friend.

Googling “where or having with aggregate sql stack overflow” returns this page, where you will find your answer. (Hint: use a subquery).

Happy coding!