Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Retrieve subject name with max mark


Retrieve subject name with max mark

Author
Message
hoanggiathanhsingles
hoanggiathanhsingles
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 5
Dear all
I had a problem when trying to retrieve the name of subject with max mark
I had two table as following:
Subject table:
create table [Subject]
(
SubID int identity(1,1) primary key,
SubName nvarchar(30) not null,
SubCredit tinyint not null constraint Default_value_Subect default(1) constraint Check_value_Subject check(SubCredit>=1),
SubStatus bit constraint default_value_SubStatus default(1)
)
Mark table:
create table Marks
(
MarkID int identity(1,1) primary key,
SubID int foreign key references [Subject](SubID),
StudentID int foreign key references Students(StudentID),
Mark float constraint Check_value_Mark check(Mark>=0 and Mark <=100) constraint Default_value_Mark Default(0),
ExamTime tinyint constraint Default_value_Examtime default(1)
)

I use this SQL statement and i can retrieve SubID with max mark
select Marks.SubID, MAX(Marks.Mark)as diem from Marks group by Marks.SubID

But otherwise, I want to display the name of Subject in stead of SubID
How can i do this? Please show me how!
Thank alot
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7474 Visits: 15142
Put what you've already got in a Common Table Expression, then join to the Subject table to get SubName.

John
hoanggiathanhsingles
hoanggiathanhsingles
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 5
Jonh!
Thank for your attention in this, but would you like to show me how more clearly?
I am a newbie in this language.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16626 Visits: 17024
No need for a cte to do this. Just join from your Marks table to the Subjects table.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7474 Visits: 15142
No need for a cte to do this.

The reason for the CTE is because he wants SubName instead of SubID. Also, if SubName isn't unique, that would mess up the grouping with a straight join.

Thank for your attention in this, but would you like to show me how more clearly?

A Common Table Expression can be thought of as a derived table. So you would start your query like this:

WITH MyCTE AS (
select Marks.SubID, MAX(Marks.Mark)as diem from Marks group by Marks.SubID
)



and then write a query as if MyCTE were the name of a table.

John
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16626 Visits: 17024
John Mitchell-245523 (12/16/2013)
No need for a cte to do this.

The reason for the CTE is because he wants SubName instead of SubID. Also, if SubName isn't unique, that would mess up the grouping with a straight join.



You can easily handle this by adding SubID to the grouping.


insert Subject
select 'Subject One', 3, 1 union all
select 'Subject One', 3, 1

insert Marks
select 1, 1, 88.9, 22 union all
select 2, 2, 55, 1

select s.SubName, MAX(m.Mark)as diem
from Marks m
join Subject s on s.SubID = m.SubID group by s.SubName, s.SubID



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7474 Visits: 15142
Ah yes, that works. I was thinking you need the same columns in the SELECT list as in the GROUP BY clause, but of course you don't. Thanks Sean.

John
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16626 Visits: 17024
John Mitchell-245523 (12/16/2013)
Ah yes, that works. I was thinking you need the same columns in the SELECT list as in the GROUP BY clause, but of course you don't. Thanks Sean.

John


Yes you can group by additional columns, you just can't show them in the output unless they are grouped.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
hoanggiathanhsingles
hoanggiathanhsingles
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 5
Dear all
Thank you all for spending your time in this topic, and now, i've finished this problem by following code:

select distinct [Subject].SubName, Marks.Mark from Marks

inner join [Subject] on Marks.SubID=[Subject].SubID

where Marks.Mark in(select MAX(Marks.Mark)as diem from Marks group by Marks.SubID )


Thank all again for your help! I am so lucky wheng joining this 4rum
hoanggiathanhsingles
hoanggiathanhsingles
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 5
Dear Sean!
Your code is so simple but it very excellent.
It solved all my problem
Thank you so much
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search