SQL Creating an Alias Column For a Nested Select?
Solution 1
you can use the following
SELECT p.name ,p.address, c.courseTitle ,c.courseDifficulty FROM (
SELECT personName, courseTitle, MAX(courseDifficulty) AS courseDifficulty
FROM course
GROUP BY personName
) AS c RIGHT JOIN person AS p ON p.name = c.personName
here am assuming personName is a unique. Otherwise you can use unique id over here instead on person name and add this field in select statement.
Solution 2
Try this:
SELECT c.CourseTitle, c.PersonName, c.CourseDifficulty
FROM Courses c
WHERE c.CourseDifficulty=(SELECT MAX(c2.CourseDifficulty) FROM Courses c2 WHERE c2.PersonName=c.PersonName)
Related videos on Youtube
DontFretBrett
Updated on June 04, 2022Comments
-
DontFretBrett almost 2 years
Say I have a
Person
table and aCourses
table. In thePerson
table I have the columnPersonName
. In theCourses
table, let's say I haveCourseTitle
,PersonName
andCourseDifficulty
.CourseDifficulty
is 1-4 (4 being the hardest). How do I return a list of people fromPerson
and for each person have a column that shows the most difficult class they're taking byCourseTitle
.As far as I know, I'd get the CourseTitle of the most difficult class Brett is taking by doing the following:
SELECT CourseTitle FROM Courses WHERE PersonName = 'Brett' AND CourseDifficulty = (SELECT MAX(CourseDifficulty) FROM Courses WHERE PersonName='Brett')
But how do I run that for each person in the Person table? I want the results to be something like
Brett-SQL For Dummies 4 Tim-Quantum Mechanics Jane-Thermodynamics 2
Sorry for the noobness. Thanks in advance for the help!