## Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

### Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

# 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

Cheers

-Greg

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

```SELECT * FROM ROOMS
WHERE CAPACITY = MAX(CAPACITY)
;
```

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

# +1 Greg Kaleka.

Excellent idea.

You could also do this:

```SELECT ID, MAX(CAPACITY) as CAPACITY FROM ROOMS
```

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

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.

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.

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?

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.

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.

PLUS

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;