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 trialBrendan Whiting
Front End Web Development Techdegree Graduate 84,738 Pointsusing the DISTINCT keyword with Task #1: "Patrons with Outstanding Loans"
I decided it would be a good idea to add the DISTINCT keyword to this query so I don't end up with duplicate people. I want to find distinct email addresses, not distinct first names, since two people might have the same first name.
It won't let me add the DISTINCT keyword to the 2nd column, only to the first column. If I want to make the email column distinct, I would have to return the columns in a different order than they asked me to.
This doesn't work:
SELECT patrons.first_name, DISTINCT patrons.email FROM patrons
INNER JOIN loans on loans.patron_id = patrons.id WHERE loans.returned_on IS NULL;
This works but returns the columns in a different order:
SELECT DISTINCT patrons.email, patrons.first_name FROM patrons
INNER JOIN loans on loans.patron_id = patrons.id WHERE loans.returned_on IS NULL;
How should I handle this?
2 Answers
Jason Anello
Courses Plus Student 94,610 PointsHi Brendan,
As you've discovered, the DISTINCT option has to come after SELECT but before anything else.
It doesn't apply to any particular column but to the row result as a whole. It removes duplicate rows rather than duplicate values within a column.
So if 2 rows have the same first name and email then they will be considered duplicates and one will be removed.
When you use DISTINCT the results will be the same whether you put email or first_name first with the exception of the column ordering.
The query you're probably looking for then is
SELECT DISTINCT first_name, email FROM patrons
INNER JOIN loans on loans.patron_id = patrons.id WHERE returned_on IS NULL
Angel Avendaño
5,013 PointsIs this correct?
SELECT p.first_name, p.email FROM patrons AS p INNER JOIN loans AS l ON p.Id = l.patron_id WHERE returned_on IS NULL GROUP BY p.first_name;
Brendan Whiting
Front End Web Development Techdegree Graduate 84,738 PointsBrendan Whiting
Front End Web Development Techdegree Graduate 84,738 PointsThanks for explaining!