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.

Start your free trial

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

Greg Kaleka
Greg Kaleka
39,021 Points
Posted by Greg Kaleka
Greg Kaleka
39,021 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,877 Points
Matthew Stevenson
Matthew Stevenson
4,877 Points

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

SELECT * FROM ROOMS
WHERE CAPACITY = MAX(CAPACITY)
;
Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

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

Michael Hill
Michael Hill
3,960 Points

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;
Sumiya Malik
Sumiya Malik
3,976 Points

SELECT ID, MAX(CAPACITY) AS LARGEST_ROOM FROM ROOMS

7 Answers

nfs
nfs
35,526 Points
nfs
nfs
35,526 Points

+1 Greg Kaleka.

Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

Excellent idea. :+1:

You could also do this:

SELECT ID, MAX(CAPACITY) as CAPACITY FROM ROOMS
Mia Filisch
Mia Filisch
16,117 Points
Mia Filisch
Mia Filisch
16,117 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".

kaganulucay
seal-mask
.a{fill-rule:evenodd;}techdegree
kaganulucay
Full Stack JavaScript Techdegree Student 16,491 Points
kaganulucay
.a{fill-rule:evenodd;}techdegree
kaganulucay
Full Stack JavaScript Techdegree Student 16,491 Points

Here is my solution:

SELECT capacity FROM rooms ORDER BY capacity DESC LIMIT 1
kevin hudson
kevin hudson
Courses Plus Student 11,987 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
231,269 Points
Steven Parker
Steven Parker
231,269 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".

Ryan Leadenham
Ryan Leadenham
139 Points
Ryan Leadenham
Ryan Leadenham
139 Points

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
231,269 Points
Steven Parker
Steven Parker
231,269 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
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.

Ryan Leadenham
Ryan Leadenham
139 Points
Ryan Leadenham
Ryan Leadenham
139 Points

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,021 Points
Greg Kaleka
Greg Kaleka
39,021 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
Marcus Grant
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;