Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
TO find max marks of students
CREATE TABLE Student ( StudentId int, Name nvarchar(30), Details nvarchar(30) ) CREATE TABLE Subject ( SubjectId int, Name nvarchar(30) ) CREATE TABLE Marks ( StudentId int, SubjectId int, Mark int ) INSERT INTO Student (StudentId, Name, Details) VALUES (1,'Alfred','AA'), (2,'Betty','BB'), (3,'Chris','CC') INSERT INTO Subject (SubjectId, Name) VALUES (1,'Maths'), (2, 'Science'), (3, 'English') INSERT INTO Marks (StudentId, SubjectId, Mark) VALUES (1,1,61),(1,2,75),(1,3,87), (2,1,82),(2,2,64),(2,3,77), (3,1,82),(3,2,83),(3,3,67) ;WITH MaxMarks AS ( SELECT SubjectId, MAX(Mark) as MaxMark FROM Marks GROUP BY SubjectId ) SELECT s.Name as [StudentName], sub.Name AS [SubjectName],m.Mark FROM MaxMarks mm INNER JOIN Marks m ON m.SubjectId = mm.SubjectId AND m.Mark = mm.MaxMark INNER JOIN Student s ON s.StudentId = m.StudentId INNER JOIN Subject sub ON sub.SubjectId = mm.SubjectId
run
|
edit
|
history
|
help
0
Please
log in
to post a comment.
Window Functions - ROW_NUMBER()
Usage of joins
tp3
omnichannel_example
Running Total
variables and table variables inside a cursor loop
Availible schedules
Greatest_N_Per_Group
b
Basic Except
Please log in to post a comment.