Run Code
|
API
|
Code Wall
|
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
Tbl
megha
hello
Yan_1st_wall
Generate xml
Turn column into string
1
Gatoactual
SQL Server NULL replacement with dynamic value
parameters_in_procedure-join&correlated sub-query content