Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Databases SQL Reporting by Example Day 1: Joining Tables The Biggest Room

Greg Kaleka
Greg Kaleka
39,019 Points

Here's a slightly more complex but more dynamic solution.

At the end of this video, Ben Deitch has a suggestion that you might want to return all rooms with the max capacity in case there is more than one. Rather than hard-coding in the number 40, you can use a subquery:

SELECT ID, CAPACITY FROM ROOMS
WHERE CAPACITY = (
    SELECT MAX(CAPACITY) FROM ROOMS
);

Just thought I'd share :blush:

Cheers :beers:

-Greg

Matthew Stevenson
Matthew Stevenson
4,876 Points

Just out of curiosity, why doesn't the following query work?

SELECT * FROM ROOMS
WHERE CAPACITY = MAX(CAPACITY)
;
Steven Parker
Steven Parker
218,654 Points

That's a misuse of an aggregate function. You cannot use aggregate functions in a WHERE clause or in a JOIN condition.

This is a great answer and I can't think of a better way to optimize this, but I've been having fun finding alternate solutions. Here is my take using a CTE. I used MIN for this example since there are two of them in the playground tied to this challenge.

WITH room_limit AS (
  SELECT MIN(CAPACITY) AS CAPACITY FROM ROOMS
)
Select ROOMS.ID, ROOMS.CAPACITY FROM ROOMS
JOIN room_limit ON ROOMS.CAPACITY = room_limit.CAPACITY
WHERE ROOMS.CAPACITY = room_limit.CAPACITY;

SELECT ID, MAX(CAPACITY) AS LARGEST_ROOM FROM ROOMS

7 Answers

Steven Parker
Steven Parker
218,654 Points

Excellent idea. :+1:

You could also do this:

SELECT ID, MAX(CAPACITY) as CAPACITY FROM ROOMS
Mia Filisch
Mia Filisch
15,381 Points

But this will aggregate the results so you only see one row, even when there are multiple rooms with the same max capacity... whereas the task, as you point out below, was "to return all rooms with the max capacity in case there is more than one".

Here is my solution:

SELECT capacity FROM rooms ORDER BY capacity DESC LIMIT 1
kevin hudson
kevin hudson
Courses Plus Student 11,584 Points

That's what I did but I think we are supposed to progress in complexity by using the next way of doing things. The DRY way I guess.

Steven Parker
Steven Parker
218,654 Points

But this clearly limits the output to one row. The task was "to return all rooms with the max capacity in case there is more than one".

Not only is this a better solution, it is the only correct solution. OK technically not the only one.... but the query shown in the video doesn't return the ID for the room with the largest capacity... it returns a random row then an unrelated field with the largest capacity.

Steven Parker
Steven Parker
218,654 Points

No, the video query reliably returns only the row with the maximum capacity. Are you trying it outside of the "SQL Playground", or with some other database than SQLite?

Guilherme Mergulhao
Guilherme Mergulhao
4,002 Points

Using

SELECT * FROM ROOMS WHERE CAPACITY = (SELECT CAPACITY FROM ROOMS ORDER BY CAPACITY DESC LIMIT 1);

will work as well.

I use MySQL and MariaDB and I'm sure a significant portion of the teamtreehouse audience will as well. Consider this example (Run in the playground).

SELECT *, MAX(capacity), MIN(capacity) FROM rooms

I guess my thought is that, with this behavior, SQLite is in the minority. But I can' t find any references to back this up, so I could be wrong. Either way, with this being framed as an "SQL" class, the particular query in the video should not be recommended for this purpose.

Greg Kaleka
Greg Kaleka
39,019 Points

Hey Ryan,

Unfortunately there is no universal SQL syntax that would cover all common DB implementations. You'll find some suggestions for resources on DBs other than SQLite in the teachers notes. If you come across something like this, you can always send an email to support@teamtreehouse.com to suggest adding some additional teachers notes about MySQL implementations.

Marcus Grant
PLUS
Marcus Grant
Courses Plus Student 2,546 Points

I am using T-SQL on Microsoft SQL Server Management Studio to run my queries. Another dynamic solution I came up with is:

SELECT TOP 1 * FROM ROOMS ORDER BY CAPACITY DESC;